<?php
class OLM_Report
{
	static function StoragePeriod($condition)
	{
		$period = Helper_BSS_Normal::getPeriod($condition['begin_time'], $condition['end_time'] . ' 23:59:59', $condition['period']);
		$where = empty($condition['warehouse_id']) ? '' : 'id=' . $condition['warehouse_id'];
		$warehouse = Warehouse::find($where)->asArray()->getAll();
		$result = array();
		if (isset($condition['by_product_categories']))
		{
			$product_categories_id = (isset($condition['by_product_categories']) && $condition['product_categories_id']) ? $condition['product_categories_id'] : 0;
			$categories = Product_Categories::find('parent_id=?', $product_categories_id)->asArray()->getAll();
			foreach ($categories as $k => $c)
			{
				$cat_ids = Product_Categories::getOffspringById($c['id']);
				array_push($cat_ids, $c['id']);
				$categories[$k]['offspring'] = implode(',', $cat_ids);
			}
			foreach ($period as $k => $p)
			{
				$result[$k]['begin_time'] = $p['begin'];
				$result[$k]['end_time'] = $p['end'];
				foreach ($warehouse as $w)
				{
					$where = ' and inventory_log.warehouse_id=' . $w['id'];
					foreach ($categories as $c)
					{
						$result[$k]['warehouse'][$w['id']]['warehouse_name'] = $w['name'];
						$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['categories_name'] = $c['name'];
						if (isset($result[$k-1]['warehouse'][$w['id']]['categories'][$c['id']]))
						{
							$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['init_quantity'] = $result[$k-1]['warehouse'][$w['id']]['categories'][$c['id']]['balance_quantity'];
							$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['init_cost'] = $result[$k-1]['warehouse'][$w['id']]['categories'][$c['id']]['balance_cost'];
						}
						else
						{
							$init = QDB::getConn()->execute('select sum(init_quantity) as init_quantity, sum(cost_amount) as init_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.init_quantity>0 and inventory_log.time<\'' . $p['begin'] . '\' and product_basic.product_categories_id in (' . $c['offspring'] . ')' . $where)->fetchRow();
							$in = QDB::getConn()->execute('select sum(in_quantity) as in_quantity, sum(cost_amount) as in_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.in_quantity>0 and inventory_log.time<\'' . $p['begin'] . '\' and product_basic.product_categories_id in (' . $c['offspring'] . ')' . $where)->fetchRow();
							$out = QDB::getConn()->execute('select sum(out_quantity) as out_quantity, sum(cost_amount) as out_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.out_quantity>0 and inventory_log.time<\'' . $p['begin'] . '\' and product_basic.product_categories_id in (' . $c['offspring'] . ')' . $where)->fetchRow();
							$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['init_quantity'] = $init['init_quantity'] + $in['in_quantity'] - $out['out_quantity'];
							$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['init_cost'] = $init['init_cost'] + $in['in_cost'] - $out['out_cost'];
						}
						$init = QDB::getConn()->execute('select sum(init_quantity) as init_quantity, sum(cost_amount) as init_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.init_quantity>0 and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\' and product_basic.product_categories_id in (' . $c['offspring'] . ')' . $where)->fetchRow();
						$in = QDB::getConn()->execute('select sum(in_quantity) as in_quantity, sum(cost_amount) as in_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.in_quantity>0 and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\' and product_basic.product_categories_id in (' . $c['offspring'] . ')' . $where)->fetchRow();
						$out = QDB::getConn()->execute('select sum(out_quantity) as out_quantity, sum(cost_amount) as out_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.out_quantity>0 and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\' and product_basic.product_categories_id in (' . $c['offspring'] . ')' . $where)->fetchRow();
						$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['in_quantity'] = $init['init_quantity'] + $in['in_quantity'];
						$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['in_cost'] = $init['init_cost'] + $in['in_cost'];
						$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['out_quantity'] = (int)$out['out_quantity'];
						$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['out_cost'] = (float)$out['out_cost'];
						$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['balance_quantity'] = $result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['init_quantity'] + $result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['in_quantity'] - $result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['out_quantity'];
						$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['balance_cost'] = $result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['init_cost'] + $result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['in_cost'] - $result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['out_cost'];
						$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['type'][Q::ini('custom_flag/inventory_log_type/purchase/in_or_out')][Q::ini('custom_flag/inventory_log_type/purchase/name')] = QDB::getConn()->execute('select sum(in_quantity) as in_quantity, sum(cost_amount) as in_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.in_quantity>0 and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/purchase/value') . ' and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\' and product_basic.product_categories_id in (' . $c['offspring'] . ')' . $where)->fetchRow();
						$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['type'][Q::ini('custom_flag/inventory_log_type/purchase_drawback/in_or_out')][Q::ini('custom_flag/inventory_log_type/purchase_drawback/name')] = QDB::getConn()->execute('select sum(out_quantity) as out_quantity, sum(cost_amount) as out_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.out_quantity>0 and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/purchase_drawback/value') . ' and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\' and product_basic.product_categories_id in (' . $c['offspring'] . ')' . $where)->fetchRow();
						$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['type'][Q::ini('custom_flag/inventory_log_type/sales_return/in_or_out')][Q::ini('custom_flag/inventory_log_type/sales_return/name')] = QDB::getConn()->execute('select sum(in_quantity) as in_quantity, sum(cost_amount) as in_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.in_quantity>0 and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/sales_return/value') . ' and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\' and product_basic.product_categories_id in (' . $c['offspring'] . ')' . $where)->fetchRow();
						$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['type'][Q::ini('custom_flag/inventory_log_type/sales/in_or_out')][Q::ini('custom_flag/inventory_log_type/sales/name')] = QDB::getConn()->execute('select sum(out_quantity) as out_quantity, sum(cost_amount) as out_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.out_quantity>0 and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/sales/value') . ' and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\' and product_basic.product_categories_id in (' . $c['offspring'] . ')' . $where)->fetchRow();
						$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['type'][Q::ini('custom_flag/inventory_log_type/other_in/in_or_out')][Q::ini('custom_flag/inventory_log_type/other_in/name')] = QDB::getConn()->execute('select sum(in_quantity) as in_quantity, sum(cost_amount) as in_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.in_quantity>0 and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/other_in/value') . ' and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\' and product_basic.product_categories_id in (' . $c['offspring'] . ')' . $where)->fetchRow();
						$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['type'][Q::ini('custom_flag/inventory_log_type/other_out/in_or_out')][Q::ini('custom_flag/inventory_log_type/other_out/name')] = QDB::getConn()->execute('select sum(out_quantity) as out_quantity, sum(cost_amount) as out_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.out_quantity>0 and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/other_out/value') . ' and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\' and product_basic.product_categories_id in (' . $c['offspring'] . ')' . $where)->fetchRow();
						$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['type'][Q::ini('custom_flag/inventory_log_type/allocate_out/in_or_out')][Q::ini('custom_flag/inventory_log_type/allocate_out/name')] = QDB::getConn()->execute('select sum(out_quantity) as out_quantity, sum(cost_amount) as out_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.out_quantity>0 and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/allocate_out/value') . ' and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\' and product_basic.product_categories_id in (' . $c['offspring'] . ')' . $where)->fetchRow();
						$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['type'][Q::ini('custom_flag/inventory_log_type/allocate_in/in_or_out')][Q::ini('custom_flag/inventory_log_type/allocate_in/name')] = QDB::getConn()->execute('select sum(in_quantity) as in_quantity, sum(cost_amount) as in_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.in_quantity>0 and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/allocate_in/value') . ' and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\' and product_basic.product_categories_id in (' . $c['offspring'] . ')' . $where)->fetchRow();
						$result[$k]['warehouse'][$w['id']]['categories'][$c['id']]['type'][Q::ini('custom_flag/inventory_log_type/allocate_return/in_or_out')][Q::ini('custom_flag/inventory_log_type/allocate_return/name')] = QDB::getConn()->execute('select sum(in_quantity) as in_quantity, sum(cost_amount) as in_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.in_quantity>0 and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/allocate_return/value') . ' and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\' and product_basic.product_categories_id in (' . $c['offspring'] . ')' . $where)->fetchRow();
					}
				}
			}
		}
		else
		{
			foreach ($period as $k => $p)
			{
				$result[$k]['begin_time'] = $p['begin'];
				$result[$k]['end_time'] = $p['end'];
				foreach ($warehouse as $w)
				{
					$where = ' and inventory_log.warehouse_id=' . $w['id'];
					$result[$k]['warehouse'][$w['id']]['warehouse_name'] = $w['name'];
					if (isset($result[$k-1]))
					{
						$result[$k]['warehouse'][$w['id']]['init_quantity'] = $result[$k-1]['warehouse'][$w['id']]['balance_quantity'];
						$result[$k]['warehouse'][$w['id']]['init_cost'] = $result[$k-1]['warehouse'][$w['id']]['balance_cost'];
					}
					else
					{
						$init = QDB::getConn()->execute('select sum(init_quantity) as init_quantity, sum(cost_amount) as init_cost from inventory_log where inventory_log.init_quantity>0 and inventory_log.time<\'' . $p['begin'] . '\'' . $where)->fetchRow();
						$in = QDB::getConn()->execute('select sum(in_quantity) as in_quantity, sum(cost_amount) as in_cost from inventory_log where inventory_log.in_quantity>0 and inventory_log.time<\'' . $p['begin'] . '\'' . $where)->fetchRow();
						$out = QDB::getConn()->execute('select sum(out_quantity) as out_quantity, sum(cost_amount) as out_cost from inventory_log where inventory_log.out_quantity>0 and inventory_log.time<\'' . $p['begin'] . '\'' . $where)->fetchRow();
						$result[$k]['warehouse'][$w['id']]['init_quantity'] = $init['init_quantity'] + $in['in_quantity'] - $out['out_quantity'];
						$result[$k]['warehouse'][$w['id']]['init_cost'] = $init['init_cost'] + $in['in_cost'] - $out['out_cost'];
					}
					$init = QDB::getConn()->execute('select sum(init_quantity) as init_quantity, sum(cost_amount) as init_cost from inventory_log where inventory_log.init_quantity>0 and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\'' . $where)->fetchRow();
					$in = QDB::getConn()->execute('select sum(in_quantity) as in_quantity, sum(cost_amount) as in_cost from inventory_log where inventory_log.in_quantity>0 and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\'' . $where)->fetchRow();
					$out = QDB::getConn()->execute('select sum(out_quantity) as out_quantity, sum(cost_amount) as out_cost from inventory_log where inventory_log.out_quantity>0 and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\'' . $where)->fetchRow();
					$result[$k]['warehouse'][$w['id']]['in_quantity'] = $init['init_quantity'] + $in['in_quantity'];
					$result[$k]['warehouse'][$w['id']]['in_cost'] = $init['init_cost'] + $in['in_cost'];
					$result[$k]['warehouse'][$w['id']]['out_quantity'] = (int)$out['out_quantity'];
					$result[$k]['warehouse'][$w['id']]['out_cost'] = (float)$out['out_cost'];
					$result[$k]['warehouse'][$w['id']]['balance_quantity'] = $result[$k]['warehouse'][$w['id']]['init_quantity'] + $result[$k]['warehouse'][$w['id']]['in_quantity'] - $result[$k]['warehouse'][$w['id']]['out_quantity'];
					$result[$k]['warehouse'][$w['id']]['balance_cost'] = $result[$k]['warehouse'][$w['id']]['init_cost'] + $result[$k]['warehouse'][$w['id']]['in_cost'] - $result[$k]['warehouse'][$w['id']]['out_cost'];
					$result[$k]['warehouse'][$w['id']]['type'][Q::ini('custom_flag/inventory_log_type/purchase/in_or_out')][Q::ini('custom_flag/inventory_log_type/purchase/name')] = QDB::getConn()->execute('select sum(in_quantity) as in_quantity, sum(cost_amount) as in_cost from inventory_log where inventory_log.in_quantity>0 and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/purchase/value') . ' and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\'' . $where)->fetchRow();
					$result[$k]['warehouse'][$w['id']]['type'][Q::ini('custom_flag/inventory_log_type/purchase_drawback/in_or_out')][Q::ini('custom_flag/inventory_log_type/purchase_drawback/name')] = QDB::getConn()->execute('select sum(out_quantity) as out_quantity, sum(cost_amount) as out_cost from inventory_log where inventory_log.out_quantity>0 and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/purchase_drawback/value') . ' and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\'' . $where)->fetchRow();
					$result[$k]['warehouse'][$w['id']]['type'][Q::ini('custom_flag/inventory_log_type/sales_return/in_or_out')][Q::ini('custom_flag/inventory_log_type/sales_return/name')] = QDB::getConn()->execute('select sum(in_quantity) as in_quantity, sum(cost_amount) as in_cost from inventory_log where inventory_log.in_quantity>0 and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/sales_return/value') . ' and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\'' . $where)->fetchRow();
					$result[$k]['warehouse'][$w['id']]['type'][Q::ini('custom_flag/inventory_log_type/sales/in_or_out')][Q::ini('custom_flag/inventory_log_type/sales/name')] = QDB::getConn()->execute('select sum(out_quantity) as out_quantity, sum(cost_amount) as out_cost from inventory_log where inventory_log.out_quantity>0 and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/sales/value') . ' and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\'' . $where)->fetchRow();
					$result[$k]['warehouse'][$w['id']]['type'][Q::ini('custom_flag/inventory_log_type/other_in/in_or_out')][Q::ini('custom_flag/inventory_log_type/other_in/name')] = QDB::getConn()->execute('select sum(in_quantity) as in_quantity, sum(cost_amount) as in_cost from inventory_log where inventory_log.in_quantity>0 and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/other_in/value') . ' and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\'' . $where)->fetchRow();
					$result[$k]['warehouse'][$w['id']]['type'][Q::ini('custom_flag/inventory_log_type/other_out/in_or_out')][Q::ini('custom_flag/inventory_log_type/other_out/name')] = QDB::getConn()->execute('select sum(out_quantity) as out_quantity, sum(cost_amount) as out_cost from inventory_log where inventory_log.out_quantity>0 and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/other_out/value') . ' and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\'' . $where)->fetchRow();
					$result[$k]['warehouse'][$w['id']]['type'][Q::ini('custom_flag/inventory_log_type/allocate_out/in_or_out')][Q::ini('custom_flag/inventory_log_type/allocate_out/name')] = QDB::getConn()->execute('select sum(out_quantity) as out_quantity, sum(cost_amount) as out_cost from inventory_log where inventory_log.out_quantity>0 and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/allocate_out/value') . ' and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\'' . $where)->fetchRow();
					$result[$k]['warehouse'][$w['id']]['type'][Q::ini('custom_flag/inventory_log_type/allocate_in/in_or_out')][Q::ini('custom_flag/inventory_log_type/allocate_in/name')] = QDB::getConn()->execute('select sum(in_quantity) as in_quantity, sum(cost_amount) as in_cost from inventory_log where inventory_log.in_quantity>0 and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/allocate_in/value') . ' and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\'' . $where)->fetchRow();
					$result[$k]['warehouse'][$w['id']]['type'][Q::ini('custom_flag/inventory_log_type/allocate_return/in_or_out')][Q::ini('custom_flag/inventory_log_type/allocate_return/name')] = QDB::getConn()->execute('select sum(in_quantity) as in_quantity, sum(cost_amount) as in_cost from inventory_log where inventory_log.in_quantity>0 and inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/allocate_return/value') . ' and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\'' . $where)->fetchRow();
				}
			}
		}
		return array('ack' => SUCCESS, 'message' => '', 'result' => $result);
	}
	
	static function salesStatisticsByPlatform($condition)
	{
		set_time_limit(0);	
		$period = Helper_BSS_Normal::getPeriod($condition['begin_shipped_time'], $condition['end_shipped_time'] . ' 23:59:59', $condition['period']);
		$result = array();
		$where = '';
		foreach ($period as $p)
		{
			$record = array();
			if (isset($condition['shop_id'])) $where = ' and shop.id in ('.implode(',', $condition['shop_id']).')';
			
			$order = Sales_Order::find('sales_order.'.$condition['op'].'>=\'' . $p['begin'] . '\' and sales_order.'.$condition['op'].'<=\'' . $p['end'] . '\''.$where)
				->joinLeft('shop', '', 'shop.id=sales_order.shop_id')
				->setColumns('sales_order.id, sales_order.number, shop.platform as shop_platform, sales_order.shipping_fee, sales_order.discount, sales_order.amount, sales_order.currency_rate, sales_order.postage, sales_order.postage_currency_rate')
				->asArray()->getAll();
			foreach ($order as $o)
			{
				if (! array_key_exists($o['shop_platform'], $record))
				{
					$record[$o['shop_platform']]['begin'] = $p['begin'];
					$record[$o['shop_platform']]['end'] = $p['end'];
					$record[$o['shop_platform']]['shop_platform'] = $o['shop_platform'];
					$record[$o['shop_platform']]['product_quantity'] = 0;
					$record[$o['shop_platform']]['order_quantity'] = 0;
					$record[$o['shop_platform']]['product_amount'] = 0;
					$record[$o['shop_platform']]['shipping_fee'] = 0;
					$record[$o['shop_platform']]['discount'] = 0;
					$record[$o['shop_platform']]['attr_amount'] = 0;
					$record[$o['shop_platform']]['amount'] = 0;
					$record[$o['shop_platform']]['product_cost'] = 0;
					$record[$o['shop_platform']]['postage'] = 0;
					$record[$o['shop_platform']]['return_quantity'] = 0;
					$record[$o['shop_platform']]['refund_amount'] = 0;
				}
				$item = QDB::getConn()->execute('select sum(quantity) as product_quantity, sum(sales_price * quantity) as product_amount, sum(cost_price * quantity) as product_cost from sales_order_item where sales_order_id =' . $o['id'])->fetchRow();
				$item_attr = QDB::getConn()->execute('select sum(sales_order_item_attribute.price * sales_order_item.quantity) as attr_amount from sales_order_item_attribute left join sales_order_item on sales_order_item.id=sales_order_item_attribute.sales_order_item_id where sales_order_item.sales_order_id=' . $o['id'])->fetchRow();
				$record[$o['shop_platform']]['product_quantity'] += $item['product_quantity'];
				$record[$o['shop_platform']]['order_quantity']++;
				$record[$o['shop_platform']]['product_amount'] += $item['product_amount'] * $o['currency_rate'];
				$record[$o['shop_platform']]['shipping_fee'] += $o['shipping_fee'] * $o['currency_rate'];
				$record[$o['shop_platform']]['discount'] += $o['discount'] * $o['currency_rate'];
				$record[$o['shop_platform']]['attr_amount'] += $item_attr['attr_amount'] * $o['currency_rate'];
				$record[$o['shop_platform']]['amount'] += $o['amount'] * $o['currency_rate'];
				$record[$o['shop_platform']]['product_cost'] += $item['product_cost'];
				$record[$o['shop_platform']]['postage'] += $o['postage'] * $o['postage_currency_rate'];
			}
			$sales_return = Sales_Return::find('sales_return.audit_time>=\'' . $p['begin'] . '\' and sales_return.audit_time<=\'' . $p['end'] . '\' and sales_return.status=' . Q::ini('custom_flag/sales_return_status/storaged/value').$where)
				->joinLeft('sales_return_item', '', 'sales_return_item.sales_return_id=sales_return.id')
				->joinLeft('shop', '', 'shop.id=sales_return.shop_id')
				->setColumns('shop.platform as shop_platform, sales_return_item.quantity, sales_return_item.cost_price')
				->asArray()->getAll();
			foreach ($sales_return as $sr)
			{
				if (! array_key_exists($sr['shop_platform'], $record))
				{
					$record[$sr['shop_platform']]['begin'] = $p['begin'];
					$record[$sr['shop_platform']]['end'] = $p['end'];
					$record[$sr['shop_platform']]['shop_platform'] = $sr['shop_platform'];
					$record[$sr['shop_platform']]['product_quantity'] = 0;
					$record[$sr['shop_platform']]['order_quantity'] = 0;
					$record[$sr['shop_platform']]['product_amount'] = 0;
					$record[$sr['shop_platform']]['shipping_fee'] = 0;
					$record[$sr['shop_platform']]['discount'] = 0;
					$record[$sr['shop_platform']]['attr_amount'] = 0;
					$record[$sr['shop_platform']]['amount'] = 0;
					$record[$sr['shop_platform']]['product_cost'] = 0;
					$record[$sr['shop_platform']]['postage'] = 0;
					$record[$sr['shop_platform']]['return_quantity'] = 0;
					$record[$sr['shop_platform']]['refund_amount'] = 0;
				}
				
				$record[$sr['shop_platform']]['product_cost'] -= $sr['cost_price'] * $sr['quantity'];
				$record[$sr['shop_platform']]['return_quantity'] += $sr['quantity'];
			}
			$sales_refund = Sales_Refund::find('sales_refund.refund_time>=\'' . $p['begin'] . '\' and sales_refund.refund_time<=\'' . $p['end'] . '\''.$where)
				->joinLeft('sales_order', '', 'sales_order.id=sales_refund.sales_order_id')
				->joinLeft('shop', '', 'shop.id=sales_order.shop_id')
				->setColumns('sales_refund.refund_amount, sales_refund.type, shop.platform as shop_platform, sales_order.currency_rate')
				->asArray()->getAll();
			foreach ($sales_refund as $sr)
			{
				if (! array_key_exists($sr['shop_platform'], $record))
				{
					$record[$sr['shop_platform']]['begin'] = $p['begin'];
					$record[$sr['shop_platform']]['end'] = $p['end'];
					$record[$sr['shop_platform']]['shop_platform'] = $sr['shop_platform'];
					$record[$sr['shop_platform']]['product_quantity'] = 0;
					$record[$sr['shop_platform']]['order_quantity'] = 0;
					$record[$sr['shop_platform']]['product_amount'] = 0;
					$record[$sr['shop_platform']]['shipping_fee'] = 0;
					$record[$sr['shop_platform']]['discount'] = 0;
					$record[$sr['shop_platform']]['attr_amount'] = 0;
					$record[$sr['shop_platform']]['amount'] = 0;
					$record[$sr['shop_platform']]['product_cost'] = 0;
					$record[$sr['shop_platform']]['postage'] = 0;
					$record[$sr['shop_platform']]['return_quantity'] = 0;
					$record[$sr['shop_platform']]['refund_amount'] = 0;
				}
				$record[$sr['shop_platform']]['refund_amount'] += $sr['refund_amount'] * $sr['type'] * (-1) * $sr['currency_rate'];
			}
			foreach ($record as $r)
			{
				$result[] = $r;
			}
		}
		return array('ack' => SUCCESS, 'message' => '', 'result' => $result);
	}
	
	static function salesPeriodByShop($condition)
	{
		set_time_limit(0);
		$period = Helper_BSS_Normal::getPeriod($condition['begin_shipped_time'], $condition['end_shipped_time'] . ' 23:59:59', $condition['period']);
		$result = array();
		$sales_where = $return_where = $refund_where = '1';
		if (! empty($condition['shop_id']))
		{
			$mapping = Shop_User_Mapping::checkMapping($condition['shop_id']);
			if ($mapping['ack'] == FAILURE)
			{
				return array('ack' => FAILURE, 'message' => $mapping['message']);
			}
		}
		else
		{
			$condition['shop_id'] = Shop_User_Mapping::getShopIdByUserId();
		}
		if (is_array($condition['shop_id']))
		{
			$shop = implode('\',\'', $condition['shop_id']);
			$sales_where .= ' and sales_order.shop_id in (\'' . $shop . '\')';
			$return_where .= ' and sales_return.shop_id in (\'' . $shop . '\')';
			$refund_where .= ' and sales_order.shop_id in (\'' . $shop . '\')';
		}
		if ( ! empty($condition['warehouse_id']))
		{
			$warehouse_ids = implode(',', $condition['warehouse_id']);
			$sales_where .= ' and sales_order.warehouse_id in (' . $warehouse_ids . ')';
			$return_where .= ' and sales_return.warehouse_id in (' . $warehouse_ids . ')';
			$refund_where .= ' and sales_order.warehouse_id in (' . $warehouse_ids . ')';
		}
		foreach ($period as $p)
		{
			$record = array();
			$order = Sales_Order::find($sales_where . ' and sales_order.shipped_time>=\'' . $p['begin'] . '\' and sales_order.shipped_time<=\'' . $p['end'] . '\'')
				->setColumns('id, number, shop_id, shipping_fee, discount, amount, currency_rate, postage, postage_currency_rate')
				->order('shop_id asc')->asArray()->getAll();
			foreach ($order as $o)
			{
				if (! array_key_exists($o['shop_id'], $record))
				{
					$shop = Shop::find('id=?', $o['shop_id'])->asArray()->getOne();
					$record[$o['shop_id']]['begin'] = $p['begin'];
					$record[$o['shop_id']]['end'] = $p['end'];
					$record[$o['shop_id']]['shop_name'] = $shop['name'];
					$record[$o['shop_id']]['product_quantity'] = 0;
					$record[$o['shop_id']]['order_quantity'] = 0;
					$record[$o['shop_id']]['product_amount'] = 0;
					$record[$o['shop_id']]['shipping_fee'] = 0;
					$record[$o['shop_id']]['discount'] = 0;
					$record[$o['shop_id']]['attr_amount'] = 0;
					$record[$o['shop_id']]['amount'] = 0;
					$record[$o['shop_id']]['product_cost'] = 0;
					$record[$o['shop_id']]['postage'] = 0;
					$record[$o['shop_id']]['return_quantity'] = 0;
					$record[$o['shop_id']]['refund_amount'] = 0;
				}
				$item = QDB::getConn()->execute('select sum(quantity) as product_quantity, sum(sales_price * quantity) as product_amount, sum(cost_price * quantity) as product_cost from sales_order_item where sales_order_id =' . $o['id'])->fetchRow();
				$item_attr = QDB::getConn()->execute('select sum(sales_order_item_attribute.price * sales_order_item.quantity) as attr_amount from sales_order_item_attribute left join sales_order_item on sales_order_item.id=sales_order_item_attribute.sales_order_item_id where sales_order_item.sales_order_id=' . $o['id'])->fetchRow();
				$record[$o['shop_id']]['product_quantity'] += $item['product_quantity'];
				$record[$o['shop_id']]['order_quantity']++;
				$record[$o['shop_id']]['product_amount'] += $item['product_amount'] * $o['currency_rate'];
				$record[$o['shop_id']]['shipping_fee'] += $o['shipping_fee'] * $o['currency_rate'];
				$record[$o['shop_id']]['discount'] += $o['discount'] * $o['currency_rate'];
				$record[$o['shop_id']]['attr_amount'] += $item_attr['attr_amount'] * $o['currency_rate'];
				$record[$o['shop_id']]['amount'] += $o['amount'] * $o['currency_rate'];
				$record[$o['shop_id']]['product_cost'] += $item['product_cost'];
				$record[$o['shop_id']]['postage'] += $o['postage'] * $o['postage_currency_rate'];
			}
			$sales_return = Sales_Return::find($return_where . ' and sales_return.audit_time>=\'' . $p['begin'] . '\' and sales_return.audit_time<=\'' . $p['end'] . '\' and sales_return.status=' . Q::ini('custom_flag/sales_return_status/storaged/value'))
				->joinLeft('sales_return_item', '', 'sales_return_item.sales_return_id=sales_return.id')
				->setColumns('sales_return.shop_id, sales_return_item.quantity, sales_return_item.cost_price')
				->asArray()->getAll();
			foreach ($sales_return as $sr)
			{
				if (! array_key_exists($sr['shop_id'], $record))
				{
					$shop = Shop::find('id=?', $sr['shop_id'])->asArray()->getOne();
					$record[$sr['shop_id']]['begin'] = $p['begin'];
					$record[$sr['shop_id']]['end'] = $p['end'];
					$record[$sr['shop_id']]['shop_name'] = $shop['name'];
					$record[$sr['shop_id']]['product_quantity'] = 0;
					$record[$sr['shop_id']]['order_quantity'] = 0;
					$record[$sr['shop_id']]['product_amount'] = 0;
					$record[$sr['shop_id']]['shipping_fee'] = 0;
					$record[$sr['shop_id']]['discount'] = 0;
					$record[$sr['shop_id']]['attr_amount'] = 0;
					$record[$sr['shop_id']]['amount'] = 0;
					$record[$sr['shop_id']]['product_cost'] = 0;
					$record[$sr['shop_id']]['postage'] = 0;
					$record[$sr['shop_id']]['return_quantity'] = 0;
					$record[$sr['shop_id']]['refund_amount'] = 0;
				}
				$record[$sr['shop_id']]['product_cost'] -= $sr['cost_price'] * $sr['quantity'];
				$record[$sr['shop_id']]['return_quantity'] += $sr['quantity'];
			}
			$sales_refund = Sales_Refund::find($refund_where . ' and sales_refund.refund_time>=\'' . $p['begin'] . '\' and sales_refund.refund_time<=\'' . $p['end'] . '\'')
				->joinLeft('sales_order', '', 'sales_order.id=sales_refund.sales_order_id')
				->setColumns('sales_refund.refund_amount, sales_refund.type, sales_order.shop_id, sales_order.currency_rate')
				->asArray()->getAll();
			foreach ($sales_refund as $sr)
			{
				if (! array_key_exists($sr['shop_id'], $record))
				{
					$shop = Shop::find('id=?', $sr['shop_id'])->asArray()->getOne();
					$record[$sr['shop_id']]['begin'] = $p['begin'];
					$record[$sr['shop_id']]['end'] = $p['end'];
					$record[$sr['shop_id']]['shop_name'] = $shop['name'];
					$record[$sr['shop_id']]['product_quantity'] = 0;
					$record[$sr['shop_id']]['order_quantity'] = 0;
					$record[$sr['shop_id']]['product_amount'] = 0;
					$record[$sr['shop_id']]['shipping_fee'] = 0;
					$record[$sr['shop_id']]['discount'] = 0;
					$record[$sr['shop_id']]['attr_amount'] = 0;
					$record[$sr['shop_id']]['amount'] = 0;
					$record[$sr['shop_id']]['product_cost'] = 0;
					$record[$sr['shop_id']]['postage'] = 0;
					$record[$sr['shop_id']]['return_quantity'] = 0;
					$record[$sr['shop_id']]['refund_amount'] = 0;
				}
				$record[$sr['shop_id']]['refund_amount'] += $sr['refund_amount'] * $sr['type'] * (-1) * $sr['currency_rate'];
			}
			foreach ($record as $r)
			{
				$result[] = $r;
			}
		}
		return array('ack' => SUCCESS, 'message' => '', 'result' => $result);
	}
	
	static function salesPeriodByCategory($condition)
	{
		set_time_limit(0);
		$period = Helper_BSS_Normal::getPeriod($condition['begin_shipped_time'], $condition['end_shipped_time'] . ' 23:59:59', $condition['period']);
		$categories = Product_Categories::find('parent_id=?', $condition['product_categories_id'])->asArray()->getAll();
		$result = array();
		if (empty($condition['shop_id']))
		{
			foreach ($categories as &$cat)
			{
				$cat_ids = Product_Categories::getOffspringById($cat['id']);
				array_push($cat_ids, $cat['id']);
				$cat['offspring'] = implode(',', $cat_ids);
			}
			unset($cat);
			$warehouse_where = '';
			if ( ! empty($condition['warehouse_id']))
			{
				$warehouse_where .= ' and inventory_log.warehouse_id in (' . implode(',', $condition['warehouse_id']) . ')';
			}
			foreach ($period as $p)
			{
				foreach ($categories as $c)
				{
					$sales_inventory = QDB::getConn()->execute('select sum(inventory_log.out_quantity) as product_quantity, sum(inventory_log.sales_amount * inventory_log.currency_rate) as product_amount, sum(inventory_log.cost_amount) as product_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/sales/value') . $warehouse_where . ' and product_basic.product_categories_id in (' . $c['offspring'] . ') and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\'')->fetchRow();
					$return_inventory = QDB::getConn()->execute('select sum(inventory_log.in_quantity) as return_quantity, sum(inventory_log.sales_amount * inventory_log.currency_rate) as refund_amount, sum(inventory_log.cost_amount) as return_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/sales_return/value') . $warehouse_where . ' and product_basic.product_categories_id in(' . $c['offspring'] . ') and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\''.$p['end'] . '\'')->fetchRow();
					if ($sales_inventory['product_quantity'] || $return_inventory['return_quantity'])
					{
						$attr = QDB::getConn()->execute('select sum(sales_order_item.quantity * sales_order_item_attribute.price * sales_order.currency_rate) as attr_amount from sales_order_item_attribute left join sales_order_item on sales_order_item.id=sales_order_item_attribute.sales_order_item_id left join sales_order on sales_order.id=sales_order_item.sales_order_id left join product_basic on product_basic.id=sales_order_item.product_basic_id where product_basic.product_categories_id in (' . $c['offspring'] . ') and sales_order.shipped_time>=\'' . $p['begin'] . '\' and shipped_time<=\'' . $p['end'] . '\'')->fetchRow();
						$record['begin'] = $p['begin'];
						$record['end'] = $p['end'];
						$record['categories_name'] = $c['name'];
						$record['product_quantity'] = $sales_inventory['product_quantity'];
						$record['product_amount'] = $sales_inventory['product_amount'];
						$record['attr_amount'] = $attr['attr_amount'];
						$record['product_cost'] = $sales_inventory['product_cost'] - $return_inventory['return_cost'];
						$record['return_quantity'] = $return_inventory['return_quantity'];
						$record['refund_amount'] = $return_inventory['refund_amount'];
						$result[] = $record;
					}
				}
			}
		}
		else
		{
			if (! empty($condition['shop_id']))
			{
				$mapping = Shop_User_Mapping::checkMapping($condition['shop_id']);
				if ($mapping['ack'] == FAILURE)
				{
					return array('ack' => FAILURE, 'message' => $mapping['message']);
				}
				$shop_ids = implode('\',\'', $condition['shop_id']);
				$shop = Shop::find("id in ('{$shop_ids}')")->asArray()->getAll();
			}
			if ( ! empty($condition['warehouse_id']))
			{
				$warehouse_ids = implode('\',\'', $condition['warehouse_id']);
			}
			foreach ($categories as &$cat)
			{
				$cat_ids = Product_Categories::getOffspringById($cat['id']);
				array_push($cat_ids, $cat['id']);
				$cat['offspring'] = $cat_ids;
			}
			unset($cat);
			foreach ($period as $p)
			{
				foreach ($categories as $c)
				{
					$record = array();
					$record['begin'] = $p['begin'];
					$record['end'] = $p['end'];
					$record['categories_name'] = $c['name'];
					$record['product_quantity'] = 0;
					$record['product_amount'] = 0;
					$record['attr_amount'] = 0;
					$record['product_cost'] = 0;
					$record['return_quantity'] = 0;
					$record['refund_amount'] = 0;
					foreach ($shop as $s)
					{
						if ( ! empty($warehouse_ids))
						{ 
							$item = Sales_Order_Item::find("sales_order.shop_id=? and sales_order.shipped_time>=? and sales_order.shipped_time<=? and product_basic.product_categories_id in (?) and sales_order.warehouse_id in ('{$warehouse_ids}')", $s['id'], $p['begin'], $p['end'], $c['offspring'])
								->joinLeft('sales_order', '', 'sales_order.id=sales_order_item.sales_order_id')
								->joinLeft('product_basic', '', 'product_basic.id=sales_order_item.product_basic_id')
								->setColumns('sales_order.id as sales_order_id, sales_order_item.id as sales_order_item_id, sales_order_item.quantity as product_quantity, sales_order_item.sales_price, sales_order.currency_rate')
								->asArray()->getAll();
							$return = Inventory_Log::find("inventory_log.type=? and sales_return.shop_id=? and product_basic.product_categories_id in (?) and inventory_log.time>=? and inventory_log.time<=? and inventory_log.warehouse_id in ('{$warehouse_ids}')", Q::ini('custom_flag/inventory_log_type/sales_return/value'), $s['id'], $c['offspring'], $p['begin'], $p['end'])
								->joinLeft('product_basic', '', 'product_basic.id=inventory_log.product_basic_id')
								->joinLeft('sales_return_item', '', 'sales_return_item.id=inventory_log.relate_table_id')
								->joinLeft('sales_return', '', 'sales_return.id=sales_return_item.sales_return_id')
								->setColumns('inventory_log.in_quantity, inventory_log.currency_rate, inventory_log.cost_amount, inventory_log.sales_amount')
								->asArray()->getAll();
						}
						else
						{
							$item = Sales_Order_Item::find('sales_order.shop_id=? and sales_order.shipped_time>=? and sales_order.shipped_time<=? and product_basic.product_categories_id in (?)', $s['id'], $p['begin'], $p['end'], $c['offspring'])
								->joinLeft('sales_order', '', 'sales_order.id=sales_order_item.sales_order_id')
								->joinLeft('product_basic', '', 'product_basic.id=sales_order_item.product_basic_id')
								->setColumns('sales_order.id as sales_order_id, sales_order_item.id as sales_order_item_id, sales_order_item.quantity as product_quantity, sales_order_item.sales_price, sales_order.currency_rate')
								->asArray()->getAll();
							$return = Inventory_Log::find('inventory_log.type=? and sales_return.shop_id=? and product_basic.product_categories_id in (?) and inventory_log.time>=? and inventory_log.time<=?', Q::ini('custom_flag/inventory_log_type/sales_return/value'), $s['id'], $c['offspring'], $p['begin'], $p['end'])
								->joinLeft('product_basic', '', 'product_basic.id=inventory_log.product_basic_id')
								->joinLeft('sales_return_item', '', 'sales_return_item.id=inventory_log.relate_table_id')
								->joinLeft('sales_return', '', 'sales_return.id=sales_return_item.sales_return_id')
								->setColumns('inventory_log.in_quantity, inventory_log.currency_rate, inventory_log.cost_amount, inventory_log.sales_amount')
								->asArray()->getAll();
						}
						if ($item || $return)
						{
							$shop_record = array();
							$shop_record['return_quantity'] = 0;
							$shop_record['refund_amount'] = 0;
							$shop_record['product_quantity'] = 0;
							$shop_record['product_amount'] = 0;
							$shop_record['attr_amount'] = 0;
							$item_ids = array();
							foreach ($item as $i)
							{
								$attr = Sales_Order_Item_Attribute::find('sales_order_item_id=?', $i['sales_order_item_id'])->asArray()->getAll();
								$attr_amount = 0;
								foreach ($attr as &$a)
								{
									if ($a['prefix'] == '+')
									{
										$attr_amount += $a['price'];
									}
									else
									{
										$attr_amount -= $a['price'];
									}
								}
								unset($a);
								$item_ids[] = $i['sales_order_item_id'];
								$shop_record['product_quantity'] += $i['product_quantity'];
								$shop_record['product_amount'] += ($i['product_quantity'] * $i['sales_price']) * $i['currency_rate'];
								$shop_record['attr_amount'] += $attr_amount * $i['currency_rate'] * $i['product_quantity'];
							}
							$item_ids = empty($item_ids) ? array(0) : $item_ids; 
							$inventory_log = Inventory_Log::find('product_basic.product_categories_id in (?) and inventory_log.relate_table_id in (?) and inventory_log.type=?', $c['offspring'], $item_ids, Q::ini('custom_flag/inventory_log_type/sales/value'))
								->joinLeft('product_basic', '', 'product_basic.id=inventory_log.product_basic_id')
								->getSum('cost_amount');
							$shop_record['product_cost'] = $inventory_log;
							foreach ($return as $r)
							{
								$shop_record['return_quantity'] += $r['in_quantity'];
								$shop_record['refund_amount'] += $r['sales_amount'] * $r['currency_rate'];
								$shop_record['product_cost'] -= $r['cost_amount'];
							}
							$record['return_quantity'] += $shop_record['return_quantity'];
							$record['refund_amount'] += $shop_record['refund_amount'];
							$record['product_quantity'] += $shop_record['product_quantity'];
							$record['product_amount'] += $shop_record['product_amount'];
							$record['attr_amount'] += $shop_record['attr_amount'];
							$record['product_cost'] += $shop_record['product_cost'];
						}
					}
					if ($record['product_quantity'] || $record['return_quantity'])
					{
						$result[] = $record;
					}
				}
			}
		}
		return array('ack' => SUCCESS, 'message' => '', 'result' => $result);
	}
	
	static function salesPeriodByShopAndCategory($condition)
	{
		set_time_limit(0);
		$period = Helper_BSS_Normal::getPeriod($condition['begin_shipped_time'], $condition['end_shipped_time'] . ' 23:59:59', $condition['period']);
		$result = array();
		if ( ! empty($condition['shop_id']))
		{
			$mapping = Shop_User_Mapping::checkMapping($condition['shop_id']);
			if ($mapping['ack'] == FAILURE)
			{
				return array('ack' => FAILURE, 'message' => $mapping['message']);
			}
			$shop_ids = implode('\' , \'', $condition['shop_id']);
			$shop = Shop::find("id in ('{$shop_ids}')")->asArray()->getAll();
		}
		else
		{
			$shop = Shop_User_Mapping::getShopByUserId();
		}
		if ( ! empty($condition['warehouse_id']))
		{
			$warehouse_ids = implode('\',\'', $condition['warehouse_id']);
		}
		$categories = Product_Categories::find('parent_id=?', $condition['product_categories_id'])->asArray()->getAll();
		foreach ($categories as &$cat)
		{
			$cat_ids = Product_Categories::getOffspringById($cat['id']);
			array_push($cat_ids, $cat['id']);
			$cat['offspring'] = $cat_ids;
		}
		unset($cat);
		foreach ($period as $p)
		{
			foreach ($shop as $s)
			{
				$record = array();
				$record['begin'] = $p['begin'];
				$record['end'] = $p['end'];
				$record['shop_name'] = $s['name'];
				$record['return_quantity'] = 0;
				$record['refund_amount'] = 0;
				$record['product_quantity'] = 0;
				$record['product_amount'] = 0;
				$record['attr_amount'] = 0;
				$record['product_cost'] = 0;
				$record['categories'] = array();
				foreach ($categories as $c)
				{
					if ( ! empty($warehouse_ids))
					{
						$item = Sales_Order_Item::find("sales_order.shop_id=? and sales_order.shipped_time>=? and sales_order.shipped_time<=? and product_basic.product_categories_id in (?) and sales_order.warehouse_id in ('{$warehouse_ids}')", $s['id'], $p['begin'], $p['end'], $c['offspring'])
							->joinLeft('sales_order', '', 'sales_order.id=sales_order_item.sales_order_id')
							->joinLeft('product_basic', '', 'product_basic.id=sales_order_item.product_basic_id')
							->setColumns('sales_order.id as sales_order_id, sales_order_item.id as sales_order_item_id, sales_order_item.quantity as product_quantity, sales_order_item.sales_price, sales_order.currency_rate')
							->asArray()->getAll();
						$return = Inventory_Log::find("inventory_log.type=? and sales_return.shop_id=? and product_basic.product_categories_id in (?) and inventory_log.time>=? and inventory_log.time<=? and inventory_log.warehouse_id in ('{$warehouse_ids}')", Q::ini('custom_flag/inventory_log_type/sales_return/value'), $s['id'], $c['offspring'], $p['begin'], $p['end'])
							->joinLeft('product_basic', '', 'product_basic.id=inventory_log.product_basic_id')
							->joinLeft('sales_return_item', '', 'sales_return_item.id=inventory_log.relate_table_id')
							->joinLeft('sales_return', '', 'sales_return.id=sales_return_item.sales_return_id')
							->setColumns('inventory_log.in_quantity, inventory_log.currency_rate, inventory_log.cost_amount, inventory_log.sales_amount')
							->asArray()->getAll();
					}
					else
					{
						$item = Sales_Order_Item::find('sales_order.shop_id=? and sales_order.shipped_time>=? and sales_order.shipped_time<=? and product_basic.product_categories_id in (?)', $s['id'], $p['begin'], $p['end'], $c['offspring'])
							->joinLeft('sales_order', '', 'sales_order.id=sales_order_item.sales_order_id')
							->joinLeft('product_basic', '', 'product_basic.id=sales_order_item.product_basic_id')
							->setColumns('sales_order.id as sales_order_id, sales_order_item.id as sales_order_item_id, sales_order_item.quantity as product_quantity, sales_order_item.sales_price, sales_order.currency_rate')
							->asArray()->getAll();
						$return = Inventory_Log::find('inventory_log.type=? and sales_return.shop_id=? and product_basic.product_categories_id in (?) and inventory_log.time>=? and inventory_log.time<=?', Q::ini('custom_flag/inventory_log_type/sales_return/value'), $s['id'], $c['offspring'], $p['begin'], $p['end'])
							->joinLeft('product_basic', '', 'product_basic.id=inventory_log.product_basic_id')
							->joinLeft('sales_return_item', '', 'sales_return_item.id=inventory_log.relate_table_id')
							->joinLeft('sales_return', '', 'sales_return.id=sales_return_item.sales_return_id')
							->setColumns('inventory_log.in_quantity, inventory_log.currency_rate, inventory_log.cost_amount, inventory_log.sales_amount')
							->asArray()->getAll();
					}	
					if ($item || $return)
					{
						$cat_record = array();
						$cat_record['categories_name'] = $c['name'];
						$cat_record['return_quantity'] = 0;
						$cat_record['refund_amount'] = 0;
						$cat_record['product_quantity'] = 0;
						$cat_record['product_amount'] = 0;
						$cat_record['attr_amount'] = 0;
						$item_ids = array();
						foreach ($item as $i)
						{
							$attr = Sales_Order_Item_Attribute::find('sales_order_item_id=?', $i['sales_order_item_id'])->asArray()->getAll();
							$attr_amount = 0;
							foreach ($attr as &$a)
							{
								if ($a['prefix'] == '+')
								{
									$attr_amount += $a['price'];
								}
								else
								{
									$attr_amount -= $a['price'];
								}
							}
							unset($a);
							$item_ids[] = $i['sales_order_item_id'];
							$cat_record['product_quantity'] += $i['product_quantity'];
							$cat_record['product_amount'] += ($i['product_quantity'] * $i['sales_price']) * $i['currency_rate'];
							$cat_record['attr_amount'] += $attr_amount * $i['currency_rate'] * $i['product_quantity'];
						}
						$item_ids = empty($item_ids) ? array(0) : $item_ids; 
						$inventory_log = Inventory_Log::find('product_basic.product_categories_id in (?) and inventory_log.relate_table_id in (?) and inventory_log.type=?', $c['offspring'], $item_ids, Q::ini('custom_flag/inventory_log_type/sales/value'))
							->joinLeft('product_basic', '', 'product_basic.id=inventory_log.product_basic_id')
							->getSum('cost_amount');
						$cat_record['product_cost'] = $inventory_log;
						foreach ($return as $r)
						{
							$cat_record['return_quantity'] += $r['in_quantity'];
							$cat_record['refund_amount'] += $r['sales_amount'] * $r['currency_rate'];
							$cat_record['product_cost'] -= $r['cost_amount'];
						}
						$record['return_quantity'] += $cat_record['return_quantity'];
						$record['refund_amount'] += $cat_record['refund_amount'];
						$record['product_quantity'] += $cat_record['product_quantity'];
						$record['product_amount'] += $cat_record['product_amount'];
						$record['attr_amount'] += $cat_record['attr_amount'];
						$record['product_cost'] += $cat_record['product_cost'];
						$record['categories'][] = $cat_record;
					}
				}
				if ($record['product_quantity'] || $record['return_quantity'])
				{
					$result[] = $record;
				}
			}
		}
		return array('ack' => SUCCESS, 'message' => '', 'result' => $result);
	}
	
	static function salesPeriod($condition)
	{
		set_time_limit(0);
		$period = Helper_BSS_Normal::getPeriod($condition['begin_shipped_time'], $condition['end_shipped_time'] . ' 23:59:59', $condition['period']);
		$result = array();
		if (empty($condition['by_categories']) && empty($condition['product_categories_id']))
		{
			$sales_where = $return_where = $refund_where = '1';
			if (! empty($condition['shop_id']))
			{
				$mapping = Shop_User_Mapping::checkMapping($condition['shop_id']);
				if ($mapping['ack'] == FAILURE)
				{
					return array('ack' => FAILURE, 'message' => $mapping['message']);
				}
			}
			else
			{
				$condition['shop_id'] = Shop_User_Mapping::getShopIdByUserId();
			}
			if (is_array($condition['shop_id']))
			{
				$shop = implode('\',\'', $condition['shop_id']);
				$sales_where .= ' and sales_order.shop_id in (\'' . $shop . '\')';
				$return_where .= ' and sales_return.shop_id in (\'' . $shop . '\')';
				$refund_where .= ' and sales_order.shop_id in (\'' . $shop . '\')';
			}
			//当分类没有选上的时候，判断是否选择上仓库和店铺
			if ( ! empty($condition['warehouse_id']))
			{
				$warehouse_ids = implode(',', $condition['warehouse_id']);
				$sales_where .= ' and sales_order.warehouse_id in (' . $warehouse_ids . ')';
				$return_where .= ' and sales_return.warehouse_id in (' . $warehouse_ids . ')';
				$refund_where .= ' and sales_order.warehouse_id in (' . $warehouse_ids . ')';
			}
			foreach ($period as $p)
			{
				$record = array();
				$order = Sales_Order::find($sales_where . ' and sales_order.shipped_time>=\'' . $p['begin'] . '\' and sales_order.shipped_time<=\'' . $p['end'] . '\'')
					->setColumns('id, number, shop_id, shipping_fee, discount, amount, currency_rate, postage, postage_currency_rate')
					->order('shop_id asc')->asArray()->getAll();
				foreach ($order as $o)
				{
					if (! array_key_exists($o['shop_id'], $record))
					{
						$shop = Shop::find('id=?', $o['shop_id'])->asArray()->getOne();
						$record[$o['shop_id']]['begin'] = $p['begin'];
						$record[$o['shop_id']]['end'] = $p['end'];
						$record[$o['shop_id']]['shop_name'] = $shop['name'];
						$record[$o['shop_id']]['product_quantity'] = 0;
						$record[$o['shop_id']]['order_quantity'] = 0;
						$record[$o['shop_id']]['product_amount'] = 0;
						$record[$o['shop_id']]['shipping_fee'] = 0;
						$record[$o['shop_id']]['discount'] = 0;
						$record[$o['shop_id']]['attr_amount'] = 0;
						$record[$o['shop_id']]['amount'] = 0;
						$record[$o['shop_id']]['product_cost'] = 0;
						$record[$o['shop_id']]['postage'] = 0;
						$record[$o['shop_id']]['return_quantity'] = 0;
						$record[$o['shop_id']]['refund_amount'] = 0;
					}
					$item = QDB::getConn()->execute('select sum(quantity) as product_quantity, sum(sales_price * quantity) as product_amount, sum(cost_price * quantity) as product_cost from sales_order_item where sales_order_id =' . $o['id'])->fetchRow();
					$item_attr = QDB::getConn()->execute('select sum(sales_order_item_attribute.price * sales_order_item.quantity) as attr_amount from sales_order_item_attribute left join sales_order_item on sales_order_item.id=sales_order_item_attribute.sales_order_item_id where sales_order_item.sales_order_id=' . $o['id'])->fetchRow();
					$record[$o['shop_id']]['product_quantity'] += $item['product_quantity'];
					$record[$o['shop_id']]['order_quantity']++;
					$record[$o['shop_id']]['product_amount'] += $item['product_amount'] * $o['currency_rate'];
					$record[$o['shop_id']]['shipping_fee'] += $o['shipping_fee'] * $o['currency_rate'];
					$record[$o['shop_id']]['discount'] += $o['discount'] * $o['currency_rate'];
					$record[$o['shop_id']]['attr_amount'] += $item_attr['attr_amount'] * $o['currency_rate'];
					$record[$o['shop_id']]['amount'] += $o['amount'] * $o['currency_rate'];
					$record[$o['shop_id']]['product_cost'] += $item['product_cost'];
					$record[$o['shop_id']]['postage'] += $o['postage'] * $o['postage_currency_rate'];
				}
				$sales_return = Sales_Return::find($return_where . ' and sales_return.audit_time>=\'' . $p['begin'] . '\' and sales_return.audit_time<=\'' . $p['end'] . '\' and sales_return.status=' . Q::ini('custom_flag/sales_return_status/storaged/value'))
					->joinLeft('sales_return_item', '', 'sales_return_item.sales_return_id=sales_return.id')
					->setColumns('sales_return.shop_id, sales_return_item.quantity, sales_return_item.cost_price')
					->asArray()->getAll();
				foreach ($sales_return as $sr)
				{
					if (! array_key_exists($sr['shop_id'], $record))
					{
						$shop = Shop::find('id=?', $sr['shop_id'])->asArray()->getOne();
						$record[$sr['shop_id']]['begin'] = $p['begin'];
						$record[$sr['shop_id']]['end'] = $p['end'];
						$record[$sr['shop_id']]['shop_name'] = $shop['name'];
						$record[$sr['shop_id']]['product_quantity'] = 0;
						$record[$sr['shop_id']]['order_quantity'] = 0;
						$record[$sr['shop_id']]['product_amount'] = 0;
						$record[$sr['shop_id']]['shipping_fee'] = 0;
						$record[$sr['shop_id']]['discount'] = 0;
						$record[$sr['shop_id']]['attr_amount'] = 0;
						$record[$sr['shop_id']]['amount'] = 0;
						$record[$sr['shop_id']]['product_cost'] = 0;
						$record[$sr['shop_id']]['postage'] = 0;
						$record[$sr['shop_id']]['return_quantity'] = 0;
						$record[$sr['shop_id']]['refund_amount'] = 0;
					}
					$record[$sr['shop_id']]['product_cost'] -= $sr['cost_price'] * $sr['quantity'];
					$record[$sr['shop_id']]['return_quantity'] += $sr['quantity'];
				}
				$sales_refund = Sales_Refund::find($refund_where . ' and sales_refund.refund_time>=\'' . $p['begin'] . '\' and sales_refund.refund_time<=\'' . $p['end'] . '\'')
					->joinLeft('sales_order', '', 'sales_order.id=sales_refund.sales_order_id')
					->setColumns('sales_refund.refund_amount, sales_refund.type, sales_order.shop_id, sales_order.currency_rate')
					->asArray()->getAll();
				foreach ($sales_refund as $sr)
				{
					if (! array_key_exists($sr['shop_id'], $record))
					{
						$shop = Shop::find('id=?', $sr['shop_id'])->asArray()->getOne();
						$record[$sr['shop_id']]['begin'] = $p['begin'];
						$record[$sr['shop_id']]['end'] = $p['end'];
						$record[$sr['shop_id']]['shop_name'] = $shop['name'];
						$record[$sr['shop_id']]['product_quantity'] = 0;
						$record[$sr['shop_id']]['order_quantity'] = 0;
						$record[$sr['shop_id']]['product_amount'] = 0;
						$record[$sr['shop_id']]['shipping_fee'] = 0;
						$record[$sr['shop_id']]['discount'] = 0;
						$record[$sr['shop_id']]['attr_amount'] = 0;
						$record[$sr['shop_id']]['amount'] = 0;
						$record[$sr['shop_id']]['product_cost'] = 0;
						$record[$sr['shop_id']]['postage'] = 0;
						$record[$sr['shop_id']]['return_quantity'] = 0;
						$record[$sr['shop_id']]['refund_amount'] = 0;
					}
					$record[$sr['shop_id']]['refund_amount'] += $sr['refund_amount'] * $sr['type'] * (-1) * $sr['currency_rate'];
				}
				foreach ($record as $r)
				{
					$result[] = $r;
				}
			}
			return array('ack' => SUCCESS, 'message' => '', 'result' => $result, 'type' => 'byshop');
		}
		else
		{
			//当分类选上的时候，进行判断店铺是否选上
			if (empty($condition['by_shop']) && empty($condition['shop_id']))
			{
				$categories = Product_Categories::find('parent_id=?', $condition['product_categories_id'])->asArray()->getAll();
				foreach ($categories as &$cat)
				{
					$cat_ids = Product_Categories::getOffspringById($cat['id']);
					array_push($cat_ids, $cat['id']);
					$cat['offspring'] = implode(',', $cat_ids);
				}
				unset($cat);
				$warehouse_where = '';
				if ( ! empty($condition['warehouse_id']))
				{
					$warehouse_where .= ' and inventory_log.warehouse_id in (' . implode(',', $condition['warehouse_id']) . ')';
				}
				foreach ($period as $p)
				{
					foreach ($categories as $c)
					{
						$sales_inventory = QDB::getConn()->execute('select sum(inventory_log.out_quantity) as product_quantity, sum(inventory_log.sales_amount * inventory_log.currency_rate) as product_amount, sum(inventory_log.cost_amount) as product_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/sales/value') . $warehouse_where . ' and product_basic.product_categories_id in (' . $c['offspring'] . ') and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\'')->fetchRow();
						$return_inventory = QDB::getConn()->execute('select sum(inventory_log.in_quantity) as return_quantity, sum(inventory_log.sales_amount * inventory_log.currency_rate) as refund_amount, sum(inventory_log.cost_amount) as return_cost from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.type=' . Q::ini('custom_flag/inventory_log_type/sales_return/value') . $warehouse_where . ' and product_basic.product_categories_id in(' . $c['offspring'] . ') and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\''.$p['end'] . '\'')->fetchRow();
						if ($sales_inventory['product_quantity'] || $return_inventory['return_quantity'])
						{
							$attr = QDB::getConn()->execute('select sum(sales_order_item.quantity * sales_order_item_attribute.price * sales_order.currency_rate) as attr_amount from sales_order_item_attribute left join sales_order_item on sales_order_item.id=sales_order_item_attribute.sales_order_item_id left join sales_order on sales_order.id=sales_order_item.sales_order_id left join product_basic on product_basic.id=sales_order_item.product_basic_id where product_basic.product_categories_id in (' . $c['offspring'] . ') and sales_order.shipped_time>=\'' . $p['begin'] . '\' and shipped_time<=\'' . $p['end'] . '\'')->fetchRow();
							$record['begin'] = $p['begin'];
							$record['end'] = $p['end'];
							$record['categories_name'] = $c['name'];
							$record['product_quantity'] = $sales_inventory['product_quantity'];
							$record['product_amount'] = $sales_inventory['product_amount'];
							$record['attr_amount'] = $attr['attr_amount'];
							$record['product_cost'] = $sales_inventory['product_cost'] - $return_inventory['return_cost'];
							$record['return_quantity'] = $return_inventory['return_quantity'];
							$record['refund_amount'] = $return_inventory['refund_amount'];
							$result[] = $record;
						}
					}
				}
				return array('ack' => SUCCESS, 'message' => '', 'result' => $result, 'type' => 'bycategories');
			}
			else
			{
				if (! empty($condition['shop_id']))
				{
					$mapping = Shop_User_Mapping::checkMapping($condition['shop_id']);
					if ($mapping['ack'] == FAILURE)
					{
						return array('ack' => FAILURE, 'message' => $mapping['message']);
					}
					$shop_ids = implode('\',\'', $condition['shop_id']);
					$shop = Shop::find("id in ('{$shop_ids}')")->asArray()->getAll();
				}
				else
				{
					$shop = Shop_User_Mapping::getShopByUserId();
				}
				if ( ! empty($condition['warehouse_id']))
				{
					$warehouse_ids = implode('\',\'', $condition['warehouse_id']);
				}
				$categories = Product_Categories::find('parent_id=?', $condition['product_categories_id'])->asArray()->getAll();
				foreach ($categories as &$cat)
				{
					$cat_ids = Product_Categories::getOffspringById($cat['id']);
					array_push($cat_ids, $cat['id']);
					$cat['offspring'] = $cat_ids;
				}
				unset($cat);
				foreach ($period as $p)
				{
					foreach ($shop as $s)
					{
						//店铺和分类都选上时，对仓库是否选择进行判断
						$record = array();
						$record['begin'] = $p['begin'];
						$record['end'] = $p['end'];
						$record['shop_name'] = $s['name'];
						$record['return_quantity'] = 0;
						$record['refund_amount'] = 0;
						$record['product_quantity'] = 0;
						$record['product_amount'] = 0;
						$record['attr_amount'] = 0;
						$record['product_cost'] = 0;
						$record['categories'] = array();
						foreach ($categories as $c)
						{
							//根据判断有没有选择仓库进行选择查询语句
							if ( ! empty($warehouse_ids))
							{ 
								$item = Sales_Order_Item::find("sales_order.shop_id=? and sales_order.shipped_time>=? and sales_order.shipped_time<=? and product_basic.product_categories_id in (?) and sales_order.warehouse_id in ('{$warehouse_ids}')", $s['id'], $p['begin'], $p['end'], $c['offspring'])
									->joinLeft('sales_order', '', 'sales_order.id=sales_order_item.sales_order_id')
									->joinLeft('product_basic', '', 'product_basic.id=sales_order_item.product_basic_id')
									->setColumns('sales_order.id as sales_order_id, sales_order_item.id as sales_order_item_id, sales_order_item.quantity as product_quantity, sales_order_item.sales_price, sales_order.currency_rate')
									->asArray()->getAll();
								$return = Inventory_Log::find("inventory_log.type=? and sales_return.shop_id=? and product_basic.product_categories_id in (?) and inventory_log.time>=? and inventory_log.time<=? and inventory_log.warehouse_id in ('{$warehouse_ids}')", Q::ini('custom_flag/inventory_log_type/sales_return/value'), $s['id'], $c['offspring'], $p['begin'], $p['end'])
									->joinLeft('product_basic', '', 'product_basic.id=inventory_log.product_basic_id')
									->joinLeft('sales_return_item', '', 'sales_return_item.sales_order_item_id=inventory_log.relate_table_id')
									->joinLeft('sales_return', '', 'sales_return.id=sales_return_item.sales_return_id')
									->setColumns('inventory_log.in_quantity, inventory_log.currency_rate, inventory_log.cost_amount')
									->asArray()->getAll();
							}
							else
							{
								$item = Sales_Order_Item::find('sales_order.shop_id=? and sales_order.shipped_time>=? and sales_order.shipped_time<=? and product_basic.product_categories_id in (?)', $s['id'], $p['begin'], $p['end'], $c['offspring'])
									->joinLeft('sales_order', '', 'sales_order.id=sales_order_item.sales_order_id')
									->joinLeft('product_basic', '', 'product_basic.id=sales_order_item.product_basic_id')
									->setColumns('sales_order.id as sales_order_id, sales_order_item.id as sales_order_item_id, sales_order_item.quantity as product_quantity, sales_order_item.sales_price, sales_order.currency_rate')
									->asArray()->getAll();
								$return = Inventory_Log::find('inventory_log.type=? and sales_return.shop_id=? and product_basic.product_categories_id in (?) and inventory_log.time>=? and inventory_log.time<=?', Q::ini('custom_flag/inventory_log_type/sales_return/value'), $s['id'], $c['offspring'], $p['begin'], $p['end'])
									->joinLeft('product_basic', '', 'product_basic.id=inventory_log.product_basic_id')
									->joinLeft('sales_return_item', '', 'sales_return_item.sales_order_item_id=inventory_log.relate_table_id')
									->joinLeft('sales_return', '', 'sales_return.id=sales_return_item.sales_return_id')
									->setColumns('inventory_log.in_quantity, inventory_log.currency_rate, inventory_log.cost_amount')
									->asArray()->getAll();
							}
							if ($item || $return)
							{
								$cat_record = array();
								$cat_record['categories_name'] = $c['name'];
								$cat_record['return_quantity'] = 0;
								$cat_record['refund_amount'] = 0;
								$cat_record['product_quantity'] = 0;
								$cat_record['product_amount'] = 0;
								$cat_record['attr_amount'] = 0;
								$item_ids = array();
								foreach ($item as $i)
								{
									$attr = Sales_Order_Item_Attribute::find('sales_order_item_id=?', $i['sales_order_item_id'])->asArray()->getAll();
									$attr_amount = 0;
									foreach ($attr as &$a)
									{
										if ($a['prefix'] == '+')
										{
											$attr_amount += $a['price'];
										}
										else
										{
											$attr_amount -= $a['price'];
										}
									}
									unset($a);
									$item_ids[] = $i['sales_order_item_id'];
									$cat_record['product_quantity'] += $i['product_quantity'];
									$cat_record['product_amount'] += ($i['product_quantity'] * $i['sales_price']) * $i['currency_rate'];
									$cat_record['attr_amount'] += $attr_amount * $i['currency_rate'] * $i['product_quantity'];
								}
								$item_ids = empty($item_ids) ? array(0) : $item_ids; 
								$inventory_log = Inventory_Log::find('product_basic.product_categories_id in (?) and inventory_log.relate_table_id in (?) and inventory_log.type=?', $c['offspring'], $item_ids, Q::ini('custom_flag/inventory_log_type/sales/value'))
									->joinLeft('product_basic', '', 'product_basic.id=inventory_log.product_basic_id')
									->getSum('cost_amount');
								$cat_record['product_cost'] = $inventory_log;
								foreach ($return as $r)
								{
									$cat_record['return_quantity'] += $r['in_quantity'];
									$cat_record['product_cost'] -= $r['cost_amount'];
								}
								$record['return_quantity'] += $cat_record['return_quantity'];
								$record['product_quantity'] += $cat_record['product_quantity'];
								$record['product_amount'] += $cat_record['product_amount'];
								$record['attr_amount'] += $cat_record['attr_amount'];
								$record['product_cost'] += $cat_record['product_cost'];
								$record['categories'][] = $cat_record;
							}
						}
						if ($record['product_quantity'])
						{
							$result[] = $record;
						}
					}
				}
				return array('ack' => SUCCESS, 'message' => '', 'result' => $result, 'type' => 'byshopcategories');
			}
		}
	}
	
	static function refund($condition)
	{
		set_time_limit(0);
		$where = '1';
		if (! empty($condition['begin_refund_time']))
		{
			$where .= ' and sales_refund.refund_time>=\'' . $condition['begin_refund_time'] . '\'';
		}
		if (! empty($condition['end_refund_time']))
		{
			$where .= ' and sales_refund.refund_time<\'' . $condition['end_refund_time'] . ' 23:59:59\'';
		}
		if (! empty($condition['shop_id']))
		{
			$where .= ' and sales_order.shop_id=' . $condition['shop_id'];
		}
		$refund_count = Sales_refund::find($where)->joinLeft('sales_order', '', 'sales_order.id=sales_refund.sales_order_id')->getCount();
		$reason = array();
		foreach (Q::ini('custom_flag/sales_refund_reason') as $k => $r)
		{
			$reason[$k]['name'] = $r['name'];
			$sales_refund = Sales_Refund::find($where . ' and sales_refund.reason=' .$r['value'])
				->joinLeft('sales_order_customer_info', 'sales_order_customer_info.country as country', 'sales_order_customer_info.sales_order_id=sales_refund.sales_order_id')
				->joinLeft('sales_order', 'sales_order.shipping_method', 'sales_order.id=sales_refund.sales_order_id')
				->joinLeft('shipping_method', 'name as shipping_method_name', 'shipping_method.id=sales_order.shipping_method')
				->asArray()->getAll();
			$reason[$k]['count'] = count($sales_refund);
			$shipping_method = array();
			$country = array();
			foreach ($sales_refund as $sr)
			{
				if ( ! array_key_exists($sr['shipping_method_name'], $shipping_method))
				{
					$shipping_method[$sr['shipping_method_name']] = 0;
				}
				$shipping_method[$sr['shipping_method_name']]++;
				if ( ! array_key_exists($sr['country'], $country))
				{
					$country[$sr['country']] = 0;
				}
				$country[$sr['country']]++;
				$reason[$k]['shipping_method'] = $shipping_method;
				$reason[$k]['country'] = $country;
			}
		}
		return array('ack' => SUCCESS, 'refund_count' => $refund_count, 'reason' => $reason);
	}
	
	static function resendRate($condition)
	{
		set_time_limit(0);
		$where = '1';
		if (! empty($condition['begin_shipped_time']))
		{
			$where .= ' and sales_order.shipped_time>\'' . $condition['begin_shipped_time'] . '\'';
		}
		if (! empty($condition['end_shipped_time']))
		{
			$where .= ' and sales_order.shipped_time<=\'' . $condition['end_shipped_time'] . ' 23:59:59\'';
		}
		if (! empty($condition['shop_id']))
		{
			$where .= ' and sales_order.shop_id=' . $condition['shop_id'];
		}
		$where .= ' and sales_order.handle_status=' . Q::ini('custom_flag/sales_order_handle_status/shipped/value');
		$all_count = Sales_Order::find($where)->getCount();
		$resend_where = $where.' and sales_order.send_method=' . Q::ini('custom_flag/sales_order_send_method/resend/value');
		$resend_count = Sales_Order::find($resend_where)->getCount();
		if (isset($condition['fieldname']))
		{
			$fieldname = explode(',', $condition['fieldname']);
			$result = Helper_BSS_Normal::dimensionResend($fieldname, $resend_where, $where);
			return array('ack' => SUCCESS, 'all_count' => $all_count, 'resend_count' => $resend_count, 'result' => $result);
		}
	}
	
	static function salesAnalysisByShopCategories($condition, $shop)
	{
		set_time_limit(0);
		$where = 'sales_order.trash_status=' . Q::ini('custom_flag/sales_order_trash_status/normal/value');
		$result = array();
		if ( ! empty($condition['begin_time']))
		{
			$where .= ' and sales_order.sales_time>=\'' . $condition['begin_time'] . '\'';
		}
		if ( ! empty($condition['end_time']))
		{
			$where .= ' and sales_order.sales_time<=\'' . $condition['end_time'] . ' 23:59:59\'';
		}
		if (empty($condition['shop_id']))
		{
			
			foreach ($shop as $s)
			{
				$order = Sales_Order::find($where . " and sales_order.shop_id={$s['id']}")->asArray()->getAll();
				$quantity = 0;
				foreach ($order as $o)
				{
					$quantity += Sales_Order_Item::find('sales_order_id=?', $o['id'])->getSum('quantity');
				}
				$new_url_arr = $condition;
				$new_url_arr['shop_id'] = $s['id'];
				$result[] = array('name' => "<a href='".url('DataCenter::Sales/SalesAnalysisByShopCategories', $new_url_arr)."'>".$s['name']."</a>", 'value' => $quantity);
			}
		}
		else
		{
			$where .= ' and sales_order.shop_id=' . $condition['shop_id'];
			$order = Sales_Order::find($where)->setColumns('sales_order.id')->asArray()->getAll();
			$sales_order_id = array();
			foreach ($order as $o)
			{
				$sales_order_id[] = $o['id'];
			}
			$sales_order_ids = implode(',', $sales_order_id);
			$categories_id = 0;
			$categories_name = 'shop_cat_1';
			if ( ! empty($condition['shop_cat_1']))
			{
				$categories_id = $condition['shop_cat_1'];
				$categories_name = 'shop_cat_2';
			}
			if ( ! empty($condition['shop_cat_2']))
			{
				$categories_id = $condition['shop_cat_2'];
				$categories_name = 'shop_cat_3';
			}
			if ( ! empty($condition['shop_cat_3']))
			{
				$categories_id = $condition['shop_cat_3'];
				$categories_name = '';
			}
			$children = OLM_Zencart_Categories::getChildrenIdById($categories_id, $condition['shop_id']);
			if ($children)
			{
				foreach ($children as $c)
				{
					$offspring = OLM_Zencart_Categories::getOffspringIdById($c['categories_id'],  $condition['shop_id']);
					$product = Online_Zencart_Product::find('shop_id=? and categories_id in (?)', $condition['shop_id'], $offspring)->setColumns('product_basic_id')->asArray()->getAll();
					$product_id = array();
					foreach ($product as $p)
					{
						$product_id[] = $p['product_basic_id'];
					}
					if (count($product_id) and count($sales_order_id))
					{
						$quantity = Sales_Order_Item::find('sales_order_id in (?) and product_basic_id in (?)', $sales_order_id, $product_id)->getSum('quantity');
					}
					else
					{
						$quantity = 0;
					}
					$new_url_arr = $condition;
					$new_url_arr[$categories_name] = $c['categories_id'];
					$result[] = array('name' => "<a href='".url('DataCenter::Sales/SalesAnalysisByShopCategories', $new_url_arr)."'>".$c['categories_name']."</a>", 'value' => $quantity);
				}
			}
			else
			{
				$product = Online_Zencart_Product::find('shop_id=? and categories_id=?', $condition['shop_id'], $categories_id)->setColumns('product_basic_id')->asArray()->getAll();
				foreach ($product as $p)
				{
					if ($p['product_basic_id'] and count($sales_order_id))
					{
						$info = Product_basic::find('id=?', $p['product_basic_id'])->asArray()->getOne();
						$quantity = Sales_Order_Item::find('sales_order_id in (?) and product_basic_id=?', $sales_order_id, $p['product_basic_id'])->getSum('quantity');
						$result[] = array('name' => "<b>{$info['number']}</b> " . $info['name'], 'value' => $quantity);
					}
				}
			}
		}
		return array('ack' => SUCCESS, 'message' => '', 'result' => $result);
	}
	
	static function salesAnalysisByProductCategories($condition)
	{
		set_time_limit(0);
		$where = '1';
		if ( ! empty($condition['begin_date']))
		{
			$where .= ' and inventory_log.time>=\'' . $condition['begin_date'] . '\'';
		}
		if ( ! empty($condition['end_date']))
		{
			$where .= ' and inventory_log.time<=\'' .  $condition['end_date'] . ' 23:59:59\'';
		}
		$categories = Product_Categories::find('parent_id=0')->asArray()->getAll();
		foreach ($categories as $k => $c)
		{
			$ids = Product_Categories::getOffspringById($c['id']);
			array_push($ids, $c['id']);
			$count = Inventory_Log::find($where . ' and inventory_log.type=? and product_basic.product_categories_id in (?)', Q::ini('custom_flag/inventory_log_type/sales/value'), $ids)
				->joinLeft('product_basic', '', 'product_basic.id=inventory_log.product_basic_id')
				->getSum('out_quantity');
			if (!$count)
			{
				unset($categories[$k]);
			}
			else
			{
				$categories[$k]['count'] = $count;
			}
		}
		$categories = Helper_BSS_Array::SortByValue($categories, 'count', 'desc');
		return array('ack' => SUCCESS, 'message' => '', 'categories' => $categories);
	}
	
	static function salesOrderHandleAnalysis($condition)
	{
		set_time_limit(0);
		$where = '1';
		if ( ! empty($condition['begin_shipped_time']))
		{
			$where .= ' and sales_order.shipped_time>=\'' . $condition['begin_shipped_time'] . '\'';
		}
		if ( ! empty($condition['end_shipped_time']))
		{
			$where .= ' and sales_order.shipped_time<=\'' . $condition['end_shipped_time'] . ' 23:59:59\'';
		}
		if (isset($condition['shop_id']) && $condition['shop_id'])
		{
			$where .= ' and sales_order.shop_id=\'' . $condition['shop_id'] . '\'';
		}
		if ( ! empty($condition['number']))
		{
			$number = explode('\r\n', $condition['number']);
			$sales_number = '';
			foreach ($number as $n)
			{
				$temp = trim($n);
				$sales_number .= "'{$temp}',";
			}
			$sales_number = substr($sales_number, 0, -1);
			$where .= " and sales_order.number in ({$sales_number})";
		}
		$sales_order = Sales_Order::find($where)->setColumns('sales_order.sales_time, sales_order.make_time, sales_order.checkout_time, sales_order.print_time, sales_order.shipped_time')->asArray()->getAll();
		if ($sales_order)
		{
			$sum = array('time_1' => 0, 'time_2' => 0, 'time_3' => 0, 'time_4' => 0);
			$max = $min = array(
					'time_1' => strtotime($sales_order[0]['make_time']) - strtotime($sales_order[0]['sales_time']),
					'time_2' => strtotime($sales_order[0]['checkout_time']) - strtotime($sales_order[0]['make_time']),
					'time_3' => strtotime($sales_order[0]['print_time']) - strtotime($sales_order[0]['checkout_time']),
					'time_4' => strtotime($sales_order[0]['shipped_time']) - strtotime($sales_order[0]['print_time'])
			);
			foreach ($sales_order as $so)
			{
				$time_1 = strtotime($so['make_time']) - strtotime($so['sales_time']);
				$time_2 = strtotime($so['checkout_time']) - strtotime($so['make_time']);
				$time_3 = strtotime($so['print_time']) - strtotime($so['checkout_time']);
				$time_4 = strtotime($so['shipped_time']) - strtotime($so['print_time']);
				foreach ($min as $k => &$m)
				{
					$sum[$k] += $$k;
					if ($min[$k] > $$k)
					{
						$min[$k] = $$k;
					}
					if ($max[$k] < $$k)
					{
						$max[$k] = $$k;
					}
				}
			}
			$count = count($sales_order);
			foreach ($sum as $k => $s)
			{
				$result['avg'][$k] = sprintf('%.0f', $s/$count);
			}
			$result['max'] = $max;
			$result['min'] = $min;
			return array('ack' => SUCCESS, 'message' => '', 'count' => $count, 'result' => $result);
		}
	}
	
	static function purchaseHandleAnalysis($condition)
	{
		$where = 'purchase_order.type=' . Q::ini('custom_flag/purchase_order_type/order/value');
		if ( ! empty($condition['begin_storage_audit_time']))
		{
			$where .= ' and purchase_order.storage_audit_time>=\'' . $condition['begin_storage_audit_time'] . '\'';
		}
		if ( ! empty($condition['end_storage_audit_time']))
		{
			$where .= ' and purchase_order.storage_audit_time<=\'' . $condition['end_storage_audit_time'] . ' 23:59:59\'';
		}
		if ( ! empty($condition['number']))
		{
			$number = explode('\r\n', $condition['number']);
			$purchase_number = '';
			foreach ($number as $n)
			{
				$temp = trim($n);
				$purchase_number .= "'{$temp}',";
			}
			$purchase_number = substr($purchase_number, 0, -1);
			$where .= " and purchase_order.number in ({$purchase_number})";
		}
		$purchase = Purchase_Order::find($where)->asArray()->getAll();
		if ($purchase)
		{
			$sum = array('time_1' => 0, 'time_2' => 0, 'time_3' => 0);
			$max = $min = array(
					'time_1' => strtotime($purchase[0]['purchase_audit_time']) - strtotime($purchase[0]['make_time']),
					'time_2' => strtotime($purchase[0]['complete_time']) - strtotime($purchase[0]['purchase_audit_time']),
					'time_3' => strtotime($purchase[0]['storage_audit_time']) - strtotime($purchase[0]['complete_time'])
			);
			foreach ($purchase as $p)
			{
				$time_1 = strtotime($p['purchase_audit_time']) - strtotime($p['make_time']);
				$time_2 = strtotime($p['complete_time']) - strtotime($p['purchase_audit_time']);
				$time_3 = strtotime($p['storage_audit_time']) - strtotime($p['complete_time']);
				foreach ($min as $k => &$m)
				{
					$sum[$k] += $$k;
					if ($min[$k] > $$k)
					{
						$min[$k] = $$k;
					}
					if ($max[$k] < $$k)
					{
						$max[$k] = $$k;
					}
				}
			}
			$count = count($purchase);
			foreach ($sum as $k => $s)
			{
				$result['avg'][$k] = sprintf('%.0f', $s/$count);
			}
			$result['max'] = $max;
			$result['min'] = $min;
			return array('ack' => SUCCESS, 'message' => '', 'count' => $count, 'result' => $result);
		}
	}
	
	static function SumByTOS($condition)
	{
		set_time_limit(0);
		$product_where = $init_where = $inventory_where = '1';
		$warehouse_where = empty($condition['warehouse_id']) ? '' : ' and inventory_log.warehouse_id=' . $condition['warehouse_id'];
		if (! empty($condition['product_categories_id']))
		{
			$categories_ids = Product_Categories::getOffspringById($condition['product_categories_id']);
			array_push($categories_ids, $condition['product_categories_id']);
			$product_where .= ' and product_basic.product_categories_id in (' . implode(',', $categories_ids) . ')';
		}
		$result = Product_Basic::find($product_where)->setColumns('product_basic.id, product_basic.number, product_basic.name')->asArray()->getAll('id');
		$in_type = Helper_BSS_Normal::getIniAll('inventory_log_type', array('in_or_out' => 'in'));
		$out_type = Helper_BSS_Normal::getIniAll('inventory_log_type', array('in_or_out' => 'out'));
		$product = array();
		foreach ($result as $r)
		{
			$product[$r['id']] = $r;
			$product[$r['id']]['balance_amount'] = $product[$r['id']]['balance_quantity'] = $product[$r['id']]['out_amount'] = $product[$r['id']]['out_quantity'] = $product[$r['id']]['in_amount'] = $product[$r['id']]['in_quantity'] = $product[$r['id']]['init_amount'] = $product[$r['id']]['init_quantity'] = 0;
			foreach ($in_type as $i)
			{
				$product[$r['id']]['in'][$i['value']] = array('quantity' => 0, 'amount' => 0);
			}
			foreach ($out_type as $o)
			{
				$product[$r['id']]['out'][$o['value']] = array('quantity' => 0, 'amount' => 0);
			}
		}
		if ( ! empty($condition['begin']))
		{
			$init_where .= ' and inventory_log.time<\'' . $condition['begin'] . '\'';
			$inventory_where .= ' and inventory_log.time>=\'' . $condition['begin'] . '\'';
		}
		if ( ! empty($condition['end']))
		{
			$inventory_where .= ' and inventory_log.time<=\'' . $condition['end'] . ' 23:59:59' . '\'';
		}
		$total = array();
		$total['balance_amount'] = $total['balance_quantity'] = $total['out_amount'] = $total['out_quantity'] = $total['in_amount'] = $total['in_quantity'] = $total['init_amount'] = $total['init_quantity'] = 0;
		if ( ! empty($condition['begin']))
		{
			$inventory = QDB::getConn()->execute('select product_basic.id, sum(inventory_log.init_quantity) as init_quantity, sum(inventory_log.init_quantity*inventory_log.cost_price) as init_amount, sum(inventory_log.in_quantity) as in_quantity, sum(inventory_log.in_quantity*inventory_log.cost_price) as in_amount, sum(inventory_log.out_quantity) as out_quantity, sum(inventory_log.out_quantity*inventory_log.cost_price) as out_amount from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where ' . $init_where . $warehouse_where . ' and ' . $product_where . ' group by product_basic.id')->fetchAll();
			foreach ($inventory as $key => $i)
			{
				if (array_key_exists($i['id'], $product))
				{
					$product[$i['id']]['init_quantity'] = $i['init_quantity'] + $i['in_quantity'] - $i['out_quantity'];
					$product[$i['id']]['init_amount'] = $i['init_amount'] + $i['in_amount'] - $i['out_amount'];
					$total['init_quantity'] += $product[$i['id']]['init_quantity'];
					$total['init_amount'] += $product[$i['id']]['init_amount'];
				}
			}
		}
		foreach ($in_type as $it)
		{
			$total['in'][$it['value']]['quantity'] = $total['in'][$it['value']]['amount'] = 0;
			$in_inventory[$it['value']] = QDB::getConn()->execute('select product_basic.id, sum(inventory_log.in_quantity) as in_quantity, sum(inventory_log.cost_amount) as cost_amount from product_basic left join inventory_log on product_basic.id=inventory_log.product_basic_id where inventory_log.type=' . $it['value'] . ' and ' . $inventory_where . $warehouse_where . ' and ' . $product_where . ' group by inventory_log.product_basic_id')->fetchAll();
		}
		foreach ($out_type as $ot)
		{
			$total['out'][$ot['value']]['quantity'] = $total['out'][$ot['value']]['amount'] = 0;
			$out_inventory[$ot['value']] = QDB::getConn()->execute('select product_basic.id, sum(inventory_log.out_quantity) as out_quantity, sum(inventory_log.cost_amount) as cost_amount from product_basic left join inventory_log on product_basic.id=inventory_log.product_basic_id where inventory_log.type=' . $ot['value'] . ' and ' . $inventory_where . $warehouse_where . ' and ' . $product_where . ' group by product_basic.id')->fetchAll();
		}
		foreach ($in_inventory as $key => $ii)
		{
			foreach ($ii as $i)
			{
				if (array_key_exists($i['id'], $product))
				{
					$product[$i['id']]['in'][$key]['quantity'] = $i['in_quantity'];
					$product[$i['id']]['in'][$key]['amount'] = $i['cost_amount'];
					$product[$i['id']]['in_quantity'] += $i['in_quantity'];
					$product[$i['id']]['in_amount'] += $i['cost_amount'];
					$total['in'][$key]['quantity'] += $i['in_quantity'];
					$total['in'][$key]['amount'] += $i['cost_amount'];
					$total['in_quantity'] += $i['in_quantity'];
					$total['in_amount'] += $i['cost_amount'];
				}
			}
		}
		foreach ($out_inventory as $key => $oi)
		{
			foreach ($oi as $o)
			{
				if (array_key_exists($i['id'], $product))
				{
					$product[$o['id']]['out'][$key]['quantity'] = $o['out_quantity'];
					$product[$o['id']]['out'][$key]['amount'] = $o['cost_amount'];
					$product[$o['id']]['out_quantity'] += $o['out_quantity'];
					$product[$o['id']]['out_amount'] += $o['cost_amount'];
					$total['out'][$key]['quantity'] += $o['out_quantity'];
					$total['out'][$key]['amount'] += $o['cost_amount'];
					$total['out_quantity'] += $o['out_quantity'];
					$total['out_amount'] += $o['cost_amount'];
				}
			}
		}
		$total['balance_quantity'] = $total['init_quantity'] + $total['in_quantity'] - $total['out_quantity'];
		$total['balance_amount'] = $total['init_amount'] + $total['in_amount'] - $total['out_amount'];
		
		//第一种：PHPExcel导出excel文件
//		$col_arr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
//		include Q::ini('custom_system/lib_dir').'PHPExcel.php';
//		$objPHPExcel = new PHPExcel();
//		$objPHPExcel->getProperties()->setCreator(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
//		$objPHPExcel->getProperties()->setLastModifiedBy(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
//		$objPHPExcel->getProperties()->setTitle('存货收发汇总表');
//		$objPHPExcel->getProperties()->setSubject('存货收发汇总表');
//		$objPHPExcel->getProperties()->setDescription('存货收发汇总表-运营支撑系统');
//		$objPHPExcel->getProperties()->setKeywords('存货收发汇总表');
//		$objPHPExcel->getProperties()->setCategory('存货收发汇总表');
//		$objPHPExcel->setActiveSheetIndex(0);
//		$objPHPExcel->getProperties()->setKeywords('存货收发汇总表');
//		$objPHPExcel->getProperties()->setCategory('存货收发汇总表');
//		$objPHPExcel->setActiveSheetIndex(0);
//		$objPHPExcel->getActiveSheet()->setTitle('存货收发汇总表');
//		$objPHPExcel->getActiveSheet()->getStyle("A1:BB3")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//		$objPHPExcel->getActiveSheet()->getStyle("A1:BB3")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//		$objPHPExcel->getActiveSheet()->mergeCells('A1:B2');
//		$objPHPExcel->getActiveSheet()->setCellValue('A1', '存货');
//		$objPHPExcel->getActiveSheet()->mergeCells('C1:D2');
//		$objPHPExcel->getActiveSheet()->setCellValue('C1', '期初结存');
//		$in_col = $col_arr[count($in_type) * 2 + 3];
//		$objPHPExcel->getActiveSheet()->mergeCells("E1:{$in_col}1");
//		$objPHPExcel->getActiveSheet()->setCellValue('E1', '入库');
//		$col = 4;
//		foreach ($in_type as $it)
//		{
//			$objPHPExcel->getActiveSheet()->mergeCells("{$col_arr[$col]}2:{$col_arr[$col+1]}2");
//			$objPHPExcel->getActiveSheet()->SetCellValue("{$col_arr[$col]}2", $it['name']);
//			$col += 2;
//		}
//		$objPHPExcel->getActiveSheet()->mergeCells("{$col_arr[$col]}1:{$col_arr[$col+1]}2");
//		$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col]}1", '本期入库合计');
//		$col += 2;
//		$out_col = $col_arr[count($out_type) * 2 + $col - 1];
//		$objPHPExcel->getActiveSheet()->mergeCells("{$col_arr[$col]}1:{$out_col}1");
//		$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col]}1", '出库');
//		foreach ($out_type as $ot)
//		{
//			$objPHPExcel->getActiveSheet()->mergeCells("{$col_arr[$col]}2:{$col_arr[$col+1]}2");
//			$objPHPExcel->getActiveSheet()->SetCellValue("{$col_arr[$col]}2", $ot['name']);
//			$col += 2;
//		}
//		$objPHPExcel->getActiveSheet()->mergeCells("{$col_arr[$col]}1:{$col_arr[$col+1]}2");
//		$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col]}1", '本期出库合计');
//		$col += 2;
//		$objPHPExcel->getActiveSheet()->mergeCells("{$col_arr[$col]}1:{$col_arr[$col+1]}2");
//		$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col]}1", '期末结存');
//		$col += 2;
//		$objPHPExcel->getActiveSheet()->setCellValue('A3', '产品名称');
//		$objPHPExcel->getActiveSheet()->setCellValue('B3', '产品编号');
//		for ($i=2; $i<$col; $i++)
//		{
//			$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$i]}3", ($i % 2) ? '成本' : '数量');
//		}
//		$row = 4;
//		foreach ($product as $item)
//		{
//			$objPHPExcel->getActiveSheet()->getCell('A' . $row)->setValueExplicit($item['number'], PHPExcel_Cell_DataType::TYPE_STRING);
//			$objPHPExcel->getActiveSheet()->setCellValue('B' . $row, $item['name']);
//			$objPHPExcel->getActiveSheet()->setCellValue('C' . $row, $item['init_quantity']);
//			$objPHPExcel->getActiveSheet()->setCellValue('D' . $row, sprintf('%.2f', $item['init_amount']));
//			$col = 4;
//			foreach ($item['in'] as $ii)
//			{
//				$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col]}{$row}", sprintf('%.2f', $ii['quantity']));
//				$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col + 1]}{$row}", sprintf('%.2f', $ii['amount']));
//				$col += 2;
//			}
//			$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col]}{$row}", sprintf('%.2f', $item['in_quantity']));
//			$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col + 1]}{$row}", sprintf('%.2f', $item['in_amount']));
//			$col += 2;
//			foreach ($item['out'] as $io)
//			{
//				$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col]}{$row}", sprintf('%.2f', $io['quantity']));
//				$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col + 1]}{$row}", sprintf('%.2f', $io['amount']));
//				$col += 2;
//			}
//			$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col]}{$row}", sprintf('%.2f', $item['out_quantity']));
//			$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col + 1]}{$row}", sprintf('%.2f', $item['out_amount']));
//			$col += 2;
//			$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col]}{$row}", sprintf('%.2f', $item['balance_quantity']));
//			$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col + 1]}{$row}", sprintf('%.2f', $item['balance_amount']));
//			$row++;
//		}
//		$objPHPExcel->getActiveSheet()->getStyle("A{$row}:B{$row}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//		$objPHPExcel->getActiveSheet()->mergeCells("A{$row}:B{$row}");
//		$objPHPExcel->getActiveSheet()->setCellValue('A' . $row, '总计');
//		$objPHPExcel->getActiveSheet()->setCellValue('C' . $row, $total['init_quantity']);
//		$objPHPExcel->getActiveSheet()->setCellValue('D' . $row, $total['init_amount']);
//		$col = 4;
//		foreach ($total['in'] as $ti)
//		{
//			$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col]}{$row}", sprintf('%.2f', $ti['quantity']));
//			$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col + 1]}{$row}", sprintf('%.2f', $ti['amount']));
//			$col += 2;
//		}
//		$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col]}{$row}", sprintf('%.2f', $total['in_quantity']));
//		$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col + 1]}{$row}", sprintf('%.2f', $total['in_amount']));
//		$col += 2;
//		foreach ($total['out'] as $to)
//		{
//			$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col]}{$row}", sprintf('%.2f', $to['quantity']));
//			$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col + 1]}{$row}", sprintf('%.2f', $to['amount']));
//			$col += 2;
//		}
//		$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col]}{$row}", sprintf('%.2f', $total['out_quantity']));
//		$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col + 1]}{$row}", sprintf('%.2f', $total['out_amount']));
//		$col += 2;
//		$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col]}{$row}", sprintf('%.2f', $total['balance_quantity']));
//		$objPHPExcel->getActiveSheet()->setCellValue("{$col_arr[$col + 1]}{$row}", sprintf('%.2f', $total['balance_amount']));
//		$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
//		$file_name = 'sumbytos-' . date('YmdHis', CURRENT_TIMESTAMP) . '-' . CURRENT_USER_NAME . ".xls";
//		header("Content-Type: application/vnd.ms-excel; charset=utf-8");
//		header("Content-Type: application/octet-stream");
//		header("Content-Type: application/download");
//		header('Content-Disposition:inline');
//		header("Content-Transfer-Encoding: binary");
//		header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
//		header("Pragma: no-cache");
//		$objWriter->Save(Q::ini('custom_system/export_dir') . $file_name);
//		$export_url = Q::ini('custom_system/base_url') . Q::ini('custom_system/export_url') . $file_name;
//		return array('ack' => SUCCESS, 'export_url' => $export_url);

		//第二种：纯文本导出excel文件
		$data = '<table>';
		$data .= '<tr>';
		$data .= '<td colspan="2" rowspan="2" style="text-align: center; width: 100px;">存货</td>';
		$data .= '<td colspan="2" rowspan="2" style="text-align: center; width: 100px;">期初结存</td>';
		$data .= '<td colspan="' . count($in_type) * 2 . '" style="text-align: center">入库</td>';
		$data .= '<td colspan="2" rowspan="2" style="text-align: center; width: 100px;">本期入库合计</td>';
		$data .= '<td colspan="'. count($out_type) * 2 . '" style="text-align: center">出库</td>';
		$data .= '<td colspan="2" rowspan="2" style="text-align: center; width: 100px;">本期出库合计</td>';
		$data .= '<td colspan="2" rowspan="2" style="text-align: center; width: 100px;">期末结存</td>';
		$data .= '</tr>';
		$data .= '<tr>';
		foreach ($in_type as $it)
		{
			$data .= '<td colspan="2" style="text-align: center; width: 100px;">' . $it['name'] . '</td>';
		}
		foreach ($out_type as $ot)
		{
			$data .= '<td colspan="2" style="text-align: center; width: 100px;">' . $ot['name'] . '</td>';
		}
		$data .= '</tr>';
		$data .= '<tr>';
		$data .= '<td style="text-align: center">产品编号</td>';
		$data .= '<td style="text-align: center; height: 20px; width: 120px;">产品名称</td>';
		$row = (count($in_type) + count($out_type) + 4) * 2;
		for($i=0; $i<$row; $i++)
		{
			$data .= '<td style="text-align: center">' . (($i % 2) ? '成本' : '数量') . '</td>';
		}
		$data .= '</tr>';
		foreach ($product as $key => $item)
		{
			$data .= '<tr>';
			$data .= '<td>' . $item['number'] . '</td>';
			$data .= '<td>' . $item['name'] . '</td>';
			$data .= '<td>' . intval($item['init_quantity']) . '</td>';
			$data .= '<td>' . sprintf('%.2f', $item['init_amount']) . '</td>';
			foreach ($item['in'] as $ii)
			{
				$data .= '<td>' . intval($ii['quantity']) . '</td>';
				$data .= '<td>' . sprintf('%.2f', $ii['amount']) . '</td>';
			}
			$data .= '<td>' . intval($item['in_quantity']) . '</td>';
			$data .= '<td>' . sprintf('%.2f', $item['in_amount']) . '</td>';
			foreach ($item['out'] as $io)
			{
				$data .= '<td>' . intval($io['quantity']) . '</td>';
				$data .= '<td>' . sprintf('%.2f', $io['amount']) . '</td>';
			}
			$data .= '<td>' . intval($item['out_quantity']) . '</td>';
			$data .= '<td>' . sprintf('%.2f', $item['out_amount']) . '</td>';
			$data .= '<td>' . intval($item['balance_quantity']) . '</td>';
			$data .= '<td>' . sprintf('%.2f', $item['balance_amount']) . '</td>';
			$data .= '</tr>';
		}
		$data .= '<tr>';
		$data .= '<td colspan="2" style="text-align: center">总计</td>';
		$data .= '<td>' . intval($total['init_quantity']) . '</td>';
		$data .= '<td>' . sprintf('%.2f', $total['init_amount']) . '</td>';
		foreach ($total['in'] as $ti)
		{
			$data .= '<td>' . intval($ti['quantity']) . '</td>';
			$data .= '<td>' . sprintf('%.2f', $ti['amount']) . '</td>';
		}
		$data .= '<td>' . intval($total['in_quantity']) . '</td>';
		$data .= '<td>' . sprintf('%.2f', $total['in_amount']) . '</td>';
		foreach ($total['out'] as $to)
		{
			$data .= '<td>' . intval($to['quantity']) . '</td>';
			$data .= '<td>' . sprintf('%.2f', $to['amount']) . '</td>';
		}
		$data .= '<td>' . intval($total['out_quantity']) . '</td>';
		$data .= '<td>' . sprintf('%.2f', $total['out_amount']) . '</td>';
		$data .= '<td>' . intval($total['balance_quantity']) . '</td>';
		$data .= '<td>' . sprintf('%.2f', $total['balance_amount']) . '</td>';
		$data .= '</tr>';
		$data .= '</table>';
		$data = iconv("UTF-8", "UTF-16LE", $data);
		$data = "\xFF\xFE" . $data;
		$file_name = 'sumbytos-' . date('YmdHis', CURRENT_TIMESTAMP) . '-' . CURRENT_USER_NAME . ".xls";
		file_put_contents(Q::ini('custom_system/export_dir') . $file_name, $data);
		$export_url = Q::ini('custom_system/export_url') . $file_name;
		return array('ack' => SUCCESS, 'export_url' => $export_url);
	}
	
	static function profitAnalysis($condition)
	{
		$where = '1';
		if ( ! empty($condition['begin_shipped_time']))
		{
			$where .= ' and sales_order.shipped_time>=\'' . $condition['begin_shipped_time'] . '\'';
		}
		if ( ! empty($condition['end_shipped_time']))
		{
			$where .= ' and sales_order.shipped_time<=\'' . $condition['end_shipped_time'] . ' 23:59:59\'';
		}
		$categories = Product_Categories::getOffspringById($condition['product_categories_id']);
		array_push($categories, $condition['product_categories_id']);
		$product = Product_Basic::find('product_categories_id in (?)', $categories)->asArray()->getAll();
		$p_id = $data = array();
		foreach ($product as $p)
		{
			$p_id[] = $p['id'];
		}
		$p_id = implode(',', $p_id);
		foreach ($condition['shop_id'] as $s)
		{
			$order_id = array();
			$shop = Shop::find('id=?', $s)->asArray()->getOne();
			$sales_order_id = QDB::getConn()->execute('select distinct(sales_order_item.sales_order_id) from sales_order_item left join sales_order on sales_order.id=sales_order_item.sales_order_id where sales_order_item.product_basic_id in (' . $p_id . ') and ' . $where . ' and shop_id=' . $s)->fetchAll();
			foreach ($sales_order_id as $oid)
			{
				$order_id[] = $oid['sales_order_id'];
			}
			$data[$s] = array(
				'shopname' => $shop['name'],
				'order_count' => 0,
				'product_count' => 0,
				'order_amount' => 0,
				'cost_amount' => 0,
				'postage_amount' => 0
			);
			if ($order_id)
			{
				$order = Sales_Order::find('id in (?)', $order_id)->asArray()->getAll();
				$item = Sales_Order_Item::find('sales_order_id in (?)', $order_id)->asArray()->getAll();
				foreach ($order as $o)
				{
					$data[$s]['order_count']++;
					$data[$s]['order_amount'] += $o['amount'] * $o['currency_rate'];
					$data[$s]['postage_amount'] += $o['postage'] * $o['postage_currency_rate'];
				}
				foreach ($item as $i)
				{
					$data[$s]['product_count'] += $i['quantity'];
					$data[$s]['cost_amount'] += $i['cost_price'] * $i['quantity'];
				}
			}
		}
		return $data;
	}
	
	static function repurchaseStatistics($condition)
	{
		$where = 'sales_order.send_method=' . Q::ini('custom_flag/sales_order_send_method/normal/value') . ' and sales_order.trash_status=' . Q::ini('custom_flag/sales_order_trash_status/normal/value');
		if ( ! empty($condition['begin_sales_time']))
		{
			$where .= ' and sales_order.sales_time>=\'' . $condition['begin_sales_time'] . '\'';
		}
		if ( ! empty($condition['end_sales_time']))
		{
			$where .= ' and sales_order.sales_time<=\'' . $condition['end_sales_time'] . ' 23:59:59\'';
		}
		if ( ! empty($condition['shop_id']))
		{
			$where .= ' and sales_order.shop_id=' . $condition['shop_id'];
		}
		if ( ! empty($condition['product_categories_id']))
		{
			$product_categories_ids = Product_Categories::getOffspringById($condition['product_categories_id']);
			array_push($product_categories_ids, $condition['product_categories_id']);
			$item_where = 'product_basic.product_categories_id in (' . implode(',', $product_categories_ids) . ')';
		}
		else
		{
			$item_where = '1';
		}
		$customer = QDB::getConn()->execute('select sales_order_customer_info.email, sales_order_customer_info.fullname, sales_order_customer_info.country, count(sales_order_customer_info.email) as count from sales_order_customer_info left join sales_order on sales_order.id=sales_order_customer_info.sales_order_id where ' . $where . ' group by sales_order_customer_info.email order by count')->fetchAll();
		foreach ($customer as $key => $c)
		{
			$has_item = false;
			if ($c['count']<2)
			{
				unset($customer[$key]);
				continue;
			}
			$sales_order = Sales_Order::find($where . ' and sales_order.id=sales_order_customer_info.sales_order_id')
				->joinLeft('sales_order_customer_info', '', 'sales_order_customer_info.email=\'' . $c['email'] . '\'')
				->joinLeft('currency', '', 'currency.id=sales_order.currency_id')
				->setColumns('sales_order.id, sales_order.number, sales_order.sales_time, sales_order.amount, currency.symbol')->asArray()->getAll();
			foreach ($sales_order as $k => &$so)
			{
				$item = Sales_Order_Item::find($item_where . ' and sales_order_item.sales_order_id=?', $so['id'])
					->joinLeft('product_basic', '', 'product_basic.id=sales_order_item.product_basic_id')
					->setColumns('product_basic.number, product_basic.name, sales_order_item.quantity, sales_order_item.sales_price, sales_order_item.id, sales_order_item.product_basic_id')->asArray()->getAll();
				if ($item)
				{
					$has_item = true;
				}
				else
				{
					unset($sales_order[$k]);
					continue;
				}
				Helper_BSS_Normal::getProductImgUrl($item);
				$so['item'] = $item;
			}
			if (!$has_item)
			{
				unset($customer[$key]);
			}
			else
			{
				$customer[$key]['order'] = $sales_order;
			}
		}
		return array('ack' => SUCCESS, 'message' => '', 'result' => $customer);
	}
	
	static function productSales($condition)
	{
		$where = '1';
		if ( ! empty($condition['begin_shipped_time']))
		{
			$where .= ' and sales_order.shipped_time>=\'' . $condition['begin_shipped_time'] . ' 00:00:00\'';
		}
		if ( ! empty($condition['end_shipped_time']))
		{
			$where .= ' and sales_order.shipped_time<=\'' . $condition['end_shipped_time'] . ' 23:59:59\'';
		}
		if ( ! empty($condition['begin_sales_time']))
		{
			$where .= ' and sales_order.sales_time>=\'' . $condition['begin_sales_time'] . ' 00:00:00\'';
		}
		if ( ! empty($condition['end_sales_time']))
		{
			$where .= ' and sales_order.sales_time<=\'' . $condition['end_sales_time'] . ' 23:59:59\'';
		}
		if ( ! empty($condition['shop_id']))
		{
			$shop = implode(',', $condition['shop_id']);
			$where .= " and sales_order.shop_id in ({$shop})";
		}
		if ( ! empty($condition['product_number']))
		{
			$product_numbers = Q::Normalize($condition['product_number'], ",");
			$where .= ' and product_basic.number in (\'' . implode('\',\'', $product_numbers) . '\')';
		}
		$categories = Product_Categories::getOffspringById($condition['product_categories_id']);
		array_push($categories, $condition['product_categories_id']);
		$product = Product_Basic::find('product_categories_id in (?)', $categories)->asArray()->getAll();
		$p_id = array();
		foreach ($product as $p)
		{
			$p_id[] = $p['id'];
		}
		$p_id = implode(',', $p_id);
		$item = Sales_Order_Item::find('sales_order_item.product_basic_id in (' . $p_id . ') and ' . $where)
			->joinLeft('sales_order', '', 'sales_order.id=sales_order_item.sales_order_id')
			->joinLeft('product_basic', '', 'product_basic.id=sales_order_item.product_basic_id')
			->setColumns('product_basic.id, product_basic.name, product_basic.number, product_basic.product_categories_id, sales_order_item.quantity')->asArray()->getAll();
		Helper_BSS_Normal::getProductImgUrl($item);
		$product = array();
		foreach ($item as $i)
		{
			if (array_key_exists($i['number'], $product))
			{
				$product[$i['number']]['quantity'] += $i['quantity'];
			}
			else
			{
				$product[$i['number']] = $i;
				$result = Product_Categories::getAncestorNameById($i['product_categories_id']);
				$product[$i['number']]['product_categories'] = implode(' > ', $result);
			}
		}
		if (count($product) > 100)
		{
			include Q::ini('custom_system/lib_dir').'PHPExcel.php';
			$phpexcel = new PHPExcel();
			$phpexcel->getProperties()->setCreator(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
			$phpexcel->getProperties()->setLastModifiedBy(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
			$phpexcel->getProperties()->setTitle("产品销量报表");
			$phpexcel->getProperties()->setSubject("产品销量报表");
			$phpexcel->getProperties()->setDescription("产品销量报表-运营支撑系统");
			$phpexcel->getProperties()->setKeywords("产品销量报表");
			$phpexcel->getProperties()->setCategory("产品销量报表");
			$phpexcel->setActiveSheetIndex(0);
			$phpexcel->getActiveSheet()->setTitle("产品销量报表");
			$phpexcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
			$phpexcel->getActiveSheet()->getColumnDimension('B')->setWidth(70);
			$phpexcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);
			$phpexcel->getActiveSheet()->getColumnDimension('D')->setWidth(70);
			$phpexcel->getActiveSheet()->setCellValue('A1', "SKU");
			$phpexcel->getActiveSheet()->setCellValue('B1', "产品名称");
			$phpexcel->getActiveSheet()->setCellValue('C1', "数量");
			$phpexcel->getActiveSheet()->setCellValue('D1', "分类");
			$phpexcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
			$phpexcel->getActiveSheet()->getStyle('A1:D1')->getFont()->setBold(true);
			$phpexcel->getActiveSheet()->getStyle('A1:D1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$i = 2;
			foreach ($product as $p)
			{
				$phpexcel->getActiveSheet()->getCell('A' . $i)->setValueExplicit($p['number'], PHPExcel_Cell_DataType::TYPE_STRING);
				$phpexcel->getActiveSheet()->setCellValue('B' . $i, $p['name']);
				$phpexcel->getActiveSheet()->setCellValue('C' . $i, $p['quantity']);
				$phpexcel->getActiveSheet()->setCellValue('D' . $i, $p['product_categories']);
				$i++;
			}
			$outputFileName = "output.xls";
			$objWriter = new PHPExcel_Writer_Excel5($phpexcel);
			header("Content-Type: application/vnd.ms-execl");
			header("Content-Type: application/octet-stream");
			header("Content-Type: application/download");
			header('Content-Disposition:inline;filename="'.$outputFileName.'"');
			header("Content-Transfer-Encoding: binary");
			header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
			header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
			header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
			header("Pragma: no-cache");
			$objWriter->save('php://output');
			exit;
		}
		else
		{
			return array('ack' => SUCCESS, 'message' => '', 'result' => $product);
		}
	}
	
	static function productSalesByShop($condition)
	{
		$where = 'sales_order.trash_status=' . Q::ini('custom_flag/sales_order_trash_status/normal/value') . ' and (sales_order.handle_status=' . Q::ini('custom_flag/sales_order_handle_status/shipped/value') . ' or sales_order.handle_status=' . Q::ini('custom_flag/sales_order_handle_status/return/value') . ') and sales_order.shipping_status=' . Q::ini('custom_flag/sales_order_shipping_status/shipped/value');
		if ( ! empty($condition['begin_shipped_time']))
		{
			$where .= ' and sales_order.shipped_time>=\'' . $condition['begin_shipped_time'] . ' 00:00:00\'';
		}
		if ( ! empty($condition['end_shipped_time']))
		{
			$where .= ' and sales_order.shipped_time<=\'' . $condition['end_shipped_time'] . ' 23:59:59\'';
		}
		if ( ! empty($condition['product_number']))
		{
			$where .= ' and product_basic.number=\'' . $condition['product_number'] . '\'';
		}
		if ( ! empty($condition['warehouse_id']))
		{
			$warehouse = Warehouse::find('id=?', $condition['warehouse_id'])->asArray()->getAll();
		}
		else
		{
			$warehouse = Warehouse::find()->asArray()->getAll();
		}
		$result = array();
		$result['total_sales_quantity'] = $result['total_sales_amount'] = $result['total_product_amount'] = 0;
		foreach ($warehouse as $k => $w)
		{
			$result['report'][$k]['warehouse_name'] = $w['name'];
			$sql = 'select shop.name, sum(sales_order_item.quantity) as sales_quantity, sum(sales_order_item.sales_price*sales_order.currency_rate*sales_order_item.quantity) as sales_amount, sum(sales_order_item.cost_price*sales_order_item.quantity) as product_amount';
			$sql .= ' from sales_order_item left join sales_order on sales_order.id=sales_order_item.sales_order_id left join product_basic on product_basic.id=sales_order_item.product_basic_id left join shop on shop.id=sales_order.shop_id';
			$sql .= ' where ' . $where . ' and sales_order.warehouse_id=' . $w['id'] . ' group by shop.id';
			$report = QDB::getConn()->execute($sql)->fetchAll();
			foreach ($report as $r)
			{
				$result['total_sales_quantity'] += $r['sales_quantity'];
				$result['total_sales_amount'] += $r['sales_amount'];
				$result['total_product_amount'] += $r['product_amount'];
			}
			$result['report'][$k]['shop_report'] = $report;
		}
		return array('ack' => SUCCESS, 'message' => '', 'data' => $result);
	}
	
	static function unshippedOrderStatis($condition)
	{
		set_time_limit(0);
		$end_time = $condition['end_time'].' 23:59:59';
		$begin_time = date('Y-m-d' ,strtotime(($end_time . '- 30 days')));
		$period = Helper_BSS_Normal::getPeriod($begin_time, $end_time, 'daily');
		$where = 'sales_order.trash_status=' . Q::ini('custom_flag/sales_order_trash_status/normal/value');
		if (! empty($condition['shop_id']))
		{
			$shop = implode(',', $condition['shop_id']);
			$where .= " and sales_order.shop_id in ({$shop})";
		}
		$total = QDB::getConn()->execute('select count(id) as count, sum(amount*currency_rate) as amount from sales_order where '.$where.' and make_time>=\''.$begin_time.'\' and make_time<=\''.$end_time.'\'')->fetchRow();
		$unshipped_order_statis = array();
		foreach ($period as $k => $p)
		{
			$order = Sales_Order::find($where.' and sales_order.make_time>=\'' . $p['begin'] . '\' and sales_order.make_time<=\'' . $p['end'] . '\'')->asArray()->getAll();
			$count = $amount = $unshipped_count = $unshipped_amount = 0;
			foreach ($order as $o)
			{
				$amount += $o['amount']*$o['currency_rate']; 
				$count++;
				if ($o['shipped_time'] >= $end_time || ($o['handle_status'] != Q::ini('custom_flag/sales_order_handle_status/shipped/value') && $o['handle_status'] != Q::ini('custom_flag/sales_order_handle_status/return/value')))
				{
					$unshipped_amount += $o['amount']*$o['currency_rate'];
					$unshipped_count++;
				}
			}
			$unshipped_order_statis[$k]['date'] = date('Y-m-d', strtotime($p['begin']));
			$unshipped_order_statis[$k]['count'] = $count; 
			$unshipped_order_statis[$k]['amount'] = $amount;
			$unshipped_order_statis[$k]['unshipped_count'] = $unshipped_count;
			$unshipped_order_statis[$k]['unshipped_amount'] = $unshipped_amount;
			$unshipped_order_statis[$k]['unshipped_count_PCT'] = $unshipped_count/$total['count']*100;
			$unshipped_order_statis[$k]['unshipped_amount_PCT'] = $unshipped_amount/$total['amount']*100;
		}
		return array('ack' => SUCCESS, 'message' => '', 'result' => array_reverse($unshipped_order_statis));
	}
	
	static function inventoryReport($condition)
	{
		if (isset($condition['by_current_categories']) && $condition['by_current_categories'])
		{
			$product_categories_id = ! empty($condition['product_categories_id']) ? $condition['product_categories_id'] : 0;
			$product_categories_ids = Product_Categories::find('parent_id=?', $product_categories_id)->setColumns('id, name')->order('id')->asArray()->getAll();
		}
		else
		{
			if (empty($condition['product_categories_id']))
			{
				$product_categories_ids = array(0 => array('name' => '', 'id' => 0));
			}
			else
			{
				$product_categories_ids = Product_Categories::find('id=?', $condition['product_categories_id'])->setColumns('id, name')->order('id')->asArray()->getAll();
			}				
		}
		$end_report_time = strtotime($condition['end_report_time'] . ' 23:59:59') > strtotime('now') ? date('Y-m-d 23:59:59', strtotime('-1 days')) : $condition['end_report_time'] . ' 23:59:59';
		$period = Helper_BSS_Normal::getPeriod($condition['begin_report_time'], $end_report_time, 'daily');
		$report = $data = array();
		$total_report_quantity= $total_report_amount = 0;
		$report = $chart_data = array();
		$where = '';
		foreach ($product_categories_ids as $key => $pci)
		{
			if ( ! empty($condition['warehouse']))
			{
				$warehouse_ids = implode(',', $condition['warehouse']);
				$where = ' and inventory_log.warehouse_id in (' . $warehouse_ids . ')';
			}
			if ($pci['id'] != 0)
			{
				$categories_ids = Product_Categories::getOffspringById($pci['id']);
				array_push($categories_ids, $pci['id']);
				$where .= ' and product_basic.product_categories_id in (' . implode(',', $categories_ids) . ')';
			}
			$total_period_quantity = $total_period_amount = 0;
			$quantity = $amount = array();
			foreach ($period as $k => $p)
			{
				if ($k == 0)
				{
					$init = QDB::getConn()->execute('select sum(inventory_log.init_quantity) as quantity, sum(inventory_log.cost_amount) as amount from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.init_quantity>0 and inventory_log.time<=\'' . $p['end'] . '\'' . $where)->fetchRow();
					$in = QDB::getConn()->execute('select sum(inventory_log.in_quantity) as quantity, sum(inventory_log.cost_amount) as amount from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.in_quantity>0 and inventory_log.time<=\'' . $p['end'] . '\'' . $where)->fetchRow();
					$out = QDB::getConn()->execute('select sum(inventory_log.out_quantity) as quantity, sum(inventory_log.cost_amount) as amount from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.out_quantity>0 and inventory_log.time<=\'' . $p['end'] . '\'' . $where)->fetchRow();
					$period_quantity = $init['quantity'] + $in['quantity'] - $out['quantity'];
					$period_amount = $init['amount'] + $in['amount'] - $out['amount'];
				}
				else
				{
					$init = QDB::getConn()->execute('select sum(inventory_log.init_quantity) as quantity, sum(inventory_log.cost_amount) as amount from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.init_quantity>0 and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\'' . $where)->fetchRow();
					$in = QDB::getConn()->execute('select sum(inventory_log.in_quantity) as quantity, sum(inventory_log.cost_amount) as amount from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.in_quantity>0 and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\'' . $where)->fetchRow();
					$out = QDB::getConn()->execute('select sum(inventory_log.out_quantity) as quantity, sum(inventory_log.cost_amount) as amount from inventory_log left join product_basic on product_basic.id=inventory_log.product_basic_id where inventory_log.out_quantity>0 and inventory_log.time>=\'' . $p['begin'] . '\' and inventory_log.time<=\'' . $p['end'] . '\'' . $where)->fetchRow();
					$period_quantity += $init['quantity'] + $in['quantity'] - $out['quantity'];
					$period_amount += $init['amount'] + $in['amount'] - $out['amount'];
				}
				$total_period_quantity += $period_quantity;
				$total_period_amount += $period_amount;
				$quantity[] = array((strtotime($p['begin']) + 8 * 3600) . '000', intval($period_quantity) / 1000);
				$amount[] = array((strtotime($p['begin']) + 8 * 3600) . '000', intval($period_amount) / 10000);
			}
			$data[] = array('name' => $pci['name'] . ' 数量（千）', 'data' => $quantity);
			$data[] = array('name' => $pci['name'] . ' 金额（万）', 'data' => $amount);
			$report[$pci['id']] = array('name' => $pci['name'], 'quantity' => intval($total_period_quantity / count($period)), 'amount' => sprintf('%.2f', $total_period_amount / count($period)));
			$total_report_quantity += intval($total_period_quantity / count($period));
			$total_report_amount += sprintf('%.2f', $total_period_amount / count($period));
		}
		return array(
			'ack' => SUCCESS,
			'message' => '',
			'report' => array('total_report_quantity' => $total_report_quantity, 'total_report_amount' => $total_report_amount, 'report' => $report),
			'chart_data' => $data
		);
	}
	
	static function salesByShopAndCountry($condition)
	{
		set_time_limit(0);
		$where = '1';
		if ( ! empty($condition['begin_shipped_time']))
		{
			$where .= ' and sales_order.shipped_time>=\'' . $condition['begin_shipped_time'] . '\'';
		}
		if ( ! empty($condition['end_shipped_time']))
		{
			$where .= ' and sales_order.shipped_time<=\'' . $condition['end_shipped_time'] . ' 23:59:59\'';
		}
		if ( ! empty($condition['product_number']))
		{
			$where .= ' and product_basic.number = \'' .$condition['product_number'] .'\'';
		}
		if ( ! empty($condition['shop_id']))
		{
			$shop_id = implode('\',\'', $condition['shop_id']);
			$where .= ' and sales_order.shop_id in (\'' . $shop_id .'\')';
		}
		if ( ! empty($condition['warehouse_id']))
		{
			$where .= ' and sales_order.warehouse_id=\'' . $condition['warehouse_id'] . '\'';
		}
		$columns = array(
			'sales_order_item.id as sales_order_item_id',
			'warehouse.name as warehouse_name',
			'shop.name as shop_name',
			'product_basic.number as product_number',
			'product_basic.name as product_name',
			'sales_order_customer_info.country',
			'sales_order_item.quantity',
		);
		$report_quantity = Sales_Order_Item::find($where)
			->joinLeft('product_basic', '', 'product_basic.id = sales_order_item.product_basic_id')
			->joinLeft('sales_order', '', 'sales_order.id = sales_order_item.sales_order_id')
			->joinLeft('warehouse', '', 'warehouse.id = sales_order.warehouse_id')
			->joinLeft('shop', '', 'shop.id = sales_order.shop_id')
			->joinLeft('sales_order_customer_info', '', 'sales_order_customer_info.sales_order_id = sales_order.id')
			->setColumns($columns)->asArray()->getAll();
		if( ! empty($report_quantity))
		{
			$report = array();
			foreach ($report_quantity as $rq)
			{
				if( ! array_key_exists($rq['warehouse_name'], $report))
				{
					$report[$rq['warehouse_name']] = array('shops' => array());
				}
				if( ! array_key_exists($rq['shop_name'], $report[$rq['warehouse_name']]['shops']))
				{
					$report[$rq['warehouse_name']]['shops'][$rq['shop_name']] = array('product_numbers' => array());
				}
				if( ! array_key_exists($rq['product_number'], $report[$rq['warehouse_name']]['shops'][$rq['shop_name']]['product_numbers']))
				{
					$report[$rq['warehouse_name']]['shops'][$rq['shop_name']]['product_numbers'][$rq['product_number']] = array('countrys' => array(), 'product_name' => $rq['product_name']);
				}
				if( ! array_key_exists($rq['country'], $report[$rq['warehouse_name']]['shops'][$rq['shop_name']]['product_numbers'][$rq['product_number']]['countrys']))
				{
					$report[$rq['warehouse_name']]['shops'][$rq['shop_name']]['product_numbers'][$rq['product_number']]['countrys'][$rq['country']] = array('quantity' => 0 );
				}
				$report[$rq['warehouse_name']]['shops'][$rq['shop_name']]['product_numbers'][$rq['product_number']]['countrys'][$rq['country']]['quantity'] += $rq['quantity'];
			}
			return array(
				'ack' => SUCCESS,
				'message' => '',
				'report' => $report,
			);
		}
		return array(
			'ack' => FAILURE,
			'message' => '',
			'report' => '',
		);
	}
	
	static function CountryDistribution($condition)
	{
		set_time_limit(0);
		$where = '1';
		if ( ! empty($condition['begin_shipped_time']))
		{
			$where .= ' and sales_order.shipped_time>=\'' . $condition['begin_shipped_time'] . '\'';
		}
		if ( ! empty($condition['end_shipped_time']))
		{
			$where .= ' and sales_order.shipped_time<=\'' . $condition['end_shipped_time'] . ' 23:59:59\'';
		}
		if ( ! empty($condition['warehouse_id']))
		{
			if (is_array($condition['warehouse_id']))
			{
				$where .= ' and sales_order.warehouse_id in (' . implode(',',$condition['warehouse_id']) . ')';
			}
		}
		if ( ! empty($condition['sku']))
		{
			$product_numbers = Q::Normalize($condition['sku'], ',');
			$where .= ' and product_basic.number in (\'' . implode('\',\'', $product_numbers) . '\')';
		}
		if ( ! empty($condition['product_categories_id']))
		{
			$product_categories_ids = Product_Categories::getOffspringById($condition['product_categories_id']);
			array_push($product_categories_ids, $condition['product_categories_id']);
			$where .= ' and product_basic.product_categories_id in (' . implode(',', $product_categories_ids) . ')';
		}
		$columns = array(
			'sales_order_customer_info.country',
			'sales_order_item.quantity'
		);
		$report_quantity = Sales_Order_Item::find($where)
			->joinLeft('product_basic', '', 'product_basic.id=sales_order_item.product_basic_id')
			->joinLeft('sales_order', '', 'sales_order.id=sales_order_item.sales_order_id')
			->joinLeft('sales_order_customer_info', '', 'sales_order_customer_info.sales_order_id=sales_order.id')
			->setColumns($columns)->asArray()->getAll();
		if ( ! empty($report_quantity))
		{
			$report = array();
			foreach ($report_quantity as $rq)
			{
				if( ! array_key_exists($rq['country'], $report))
				{
					$report[$rq['country']] = array('quantity' => 0 );
				}
				$report[$rq['country']]['quantity'] += $rq['quantity'];
				$report[$rq['country']]['country'] = $rq['country'];
			}
			return array(
				'ack' => SUCCESS,
				'message' => '',
				'report' => $report,
			);
		}
		return array(
			'ack' => FAILURE,
			'message' => '',
			'report' => array(),
		);
	}
	
	static function OperationReport($condition)
	{
		set_time_limit(0);
		$where = 'sales_order.warehouse_id=' . Warehouse::getDefaultWarehouseId();
		if ( ! empty($condition['begin_shipped_time']) && ! empty($condition['end_shipped_time']) && ! empty($condition['user_id']))
		{
			$data = array();
			$period = Helper_BSS_Normal::getPeriod($condition['begin_shipped_time'], $condition['end_shipped_time'] . '23:59:59', $condition['period']);
			$user_id = implode('\',\'', $condition['user_id']);
			foreach ($period as $p)
			{
				$report['begin_time'] = $p['begin'];
				$report['end_time'] = $p['end'];
				$sales_order = Sales_Order::find($where . ' and (sales_order.pick_user_id in (\'' . $user_id . '\') or sales_order.print_address_user_id in (\'' . $user_id . '\') or sales_order.pack_user_id in (\'' . $user_id . '\') or sales_order.weigh_user_id in (\'' . $user_id . '\')) and sales_order.shipped_time>=\'' . $p['begin'] . '\' and sales_order.shipped_time<=\'' . $p['end'] . '\'')->setColumns('sales_order.product_quantity, sales_order.pick_user_id, sales_order.print_address_user_id, sales_order.pack_user_id, sales_order.weigh_user_id')->asArray()->getAll();
				$result =  array();
				if ($sales_order)
				{
					foreach ($sales_order as $so)
					{
						if ($so['product_quantity'] == 1)
						{
							if (in_array($so['pick_user_id'], $condition['user_id']))
							{
								isset($result[$so['pick_user_id']]['pick']) ? $result[$so['pick_user_id']]['pick']++ : $result[$so['pick_user_id']]['pick']=1;
							}
							if (in_array($so['print_address_user_id'], $condition['user_id']))
							{
								isset($result[$so['print_address_user_id']]['print']) ? $result[$so['print_address_user_id']]['print']++ : $result[$so['print_address_user_id']]['print']=1;
							}
							if (in_array($so['pack_user_id'], $condition['user_id']))
							{
								isset($result[$so['pack_user_id']]['pack']) ? $result[$so['pack_user_id']]['pack']++ : $result[$so['pack_user_id']]['pack']=1;
							}
						}
						else
						{
							if (in_array($so['pick_user_id'], $condition['user_id']))
							{
								isset($result[$so['pick_user_id']]['picks']) ? $result[$so['pick_user_id']]['picks']++ : $result[$so['pick_user_id']]['picks']=1;
							}
							if (in_array($so['print_address_user_id'], $condition['user_id']))
							{
								isset($result[$so['print_address_user_id']]['prints']) ? $result[$so['print_address_user_id']]['prints']++ : $result[$so['print_address_user_id']]['prints']=1;
							}
							if (in_array($so['pack_user_id'], $condition['user_id']))
							{
								isset($result[$so['pack_user_id']]['packs']) ? $result[$so['pack_user_id']]['packs']++ : $result[$so['pack_user_id']]['packs']=1;
							}
						}
						if (in_array($so['weigh_user_id'], $condition['user_id']))
						{
							isset($result[$so['weigh_user_id']]['weigh']) ? $result[$so['weigh_user_id']]['weigh']++ : $result[$so['weigh_user_id']]['weigh']=1;
							isset($result[$so['weigh_user_id']]['weigh_product_quantity']) ? ($result[$so['weigh_user_id']]['weigh_product_quantity']=$result[$so['weigh_user_id']]['weigh_product_quantity']+$so['product_quantity']) : $result[$so['weigh_user_id']]['weigh_product_quantity']=0;
						}
						if (in_array($so['pick_user_id'], $condition['user_id']))
						{
							isset($result[$so['pick_user_id']]['pick_product_quantity']) ? ($result[$so['pick_user_id']]['pick_product_quantity']=$result[$so['pick_user_id']]['pick_product_quantity']+$so['product_quantity']) : $result[$so['pick_user_id']]['pick_product_quantity']=0;  
						}
						if (in_array($so['print_address_user_id'], $condition['user_id']))
						{
							isset($result[$so['print_address_user_id']]['print_product_quantity']) ? ($result[$so['print_address_user_id']]['print_product_quantity']=$result[$so['print_address_user_id']]['print_product_quantity']+$so['product_quantity']) : $result[$so['print_address_user_id']]['print_product_quantity'] = 0;  
						}
						if (in_array($so['pack_user_id'], $condition['user_id']))
						{
							isset($result[$so['pack_user_id']]['pack_product_quantity']) ? ($result[$so['pack_user_id']]['pack_product_quantity']=$result[$so['pack_user_id']]['pack_product_quantity']+$so['product_quantity']) : $result[$so['pack_user_id']]['pack_product_quantity']=0; 
						}
						$report['result'] = $result;
					}
					$data[] = $report;
				}
			}
			return array('ack' => SUCCESS, 'message' => '', 'data' => $data);
		}
		else
		{
			return array('ack' => FAILURE, 'message' => '提交参数错误');
		}
	}
	
	static function shippingFee($condition)
	{
		set_time_limit(0);
		$period = Helper_BSS_Normal::getPeriod($condition['begin_shipped_time'], $condition['end_shipped_time'] . ' 23:59:59', $condition['period']);
		$result = array();
		$where = '';
		if (isset($condition['shop_id'])) $where = ' and sales_order.shop_id in ('.implode(',', $condition['shop_id']).')'; 
		foreach ($period as $k => $p)
		{
			$result[$k] = $p;
			$result[$k]['quantity'] = 0;
			$result[$k]['weight'] = 0;
			$result[$k]['amount'] = 0;
			$order = QDB::getConn()->execute('select sales_order.shipping_method, count(sales_order.id) as quantity, sum(sales_order.postage) as amount, sum(sales_order.weight) as weight from sales_order where sales_order.'.$condition['op'].'>=\'' . $p['begin'] . '\' and sales_order.'.$condition['op'].'<=\'' . $p['end'] . '\'' . $where . ' group by shipping_method')->fetchAll(); 
			$record = array();
			foreach ($order as $r)
			{
				$shipping_method = Shipping_Method::find('id=?', $r['shipping_method'])->asArray()->getOne();
				$record[$r['shipping_method']]['name'] = $shipping_method['name'];
				$record[$r['shipping_method']]['quantity'] = $r['quantity'];
				$record[$r['shipping_method']]['weight'] = $r['weight'];
				$record[$r['shipping_method']]['amount'] = $r['amount'];
				$result[$k]['quantity'] += $r['quantity'];
				$result[$k]['weight'] += $r['weight'];
				$result[$k]['amount'] += $r['amount'];
			}
			$result[$k]['data'] = $record;
		}
		return array('ack' => SUCCESS, 'message' => '', 'result' => $result);
	}
}